How to keep data together in excel

Question

i have a spreadsheet in excel 2016 that uses the following formulae

=IFERROR(INDEX(Attending!$A$2:$A$20,AGGREGATE(15,6,(ROW($A$2:$A$20)-ROW($A$2)+1)/(Attending!$B$2:$B$20="Yes"),ROW(1:1))),"")

This formula takes names in column A of the attending sheet and if column be against those names is set to "Yes" they show in the menu choices sheet.

but it only adds entries into column A of menu choices sheet

so then in the destination sheet entries against these peoples name in columns B, C and D get out of wack of someone else gets added above them.

how do I link a given persons B, C and D data to them so they stay in alignment when new entries are added in above them.?

Also when menu choices B1 has a value greater than empty I want attending sheets C1 to be set to a value of "Yes".

how do I do these things?

Answer 1

From the sample worksheet you provided, I can see that you have switched the columns as suggested. After checking the workbook, I think you should also add the Deposit column on the Menu Sheet and manually update the column on that sheet.

So presume you have filled out the Menu table as below:

Enter the following formula in Cell B2 in your Attending table, and drag it across to Column D and drag it down to the end of the table.

=IFERROR(IF(VLOOKUP($A2,Table2,COLUMN()+1,0)>0,"Yes","No"),"")

And enter the following formula in Cell G2 in the Attending table, and drag it down to the end of the table.

=IFERROR(VLOOKUP(A2,Table2,6,0),"")

Please note Table2 in the above formulas are the name for the table on your Menu sheet. Please replace it with the actual table name if needed.

Once done, you should have the following on Attending sheet:

Let's say later on you need to update the Menu table for AMC as below:

Your Attending table should be updated automatically as below:

---------------- EDIT #2 ----------------

As requested, I have changed Column F on the Menu Table to Amount Paid instead of Deposit, which allows the user to input the actual dollar value paid instead of the word Paid/Unpaid.

Then replace the formula in Cell G2 and H2 on the Attending Table as below, and drag them down to the last row of the table.

=IFERROR(VLOOKUP(A2,Table2,6,0),"")

=IF([@[Price:]]="","",F2-G2)

Then you should have the Amount Paid and Balance to Pay showing correctly on the Attending Table. It may not be necessary to maintain a separate column showing whether a fixed deposit is paid or not as long as it is not mandatory to pay a fixed amount up front.

Conclusion, it is A good practice to keep all manually updated data in one place, and keep all formula-driven data in another. Do not mix them up so you will not encounter the problem of hard-coded data entry does not match with formula-driven result.