INDEX across sheets; deleting rows on active sheet issue

[MaatWoodfriend]

Hello Excel Gods,

I have (what I hope) is a fairly simple issue, but have cooked my brain trying to find a solution. There's a good chance my brain was already cooked though, apologies in advance. Here's a silly, bare-bones example to illustrate:

• On my first sheet I have a database of friends invited to a party - plus columns for their details and meal preference.
• On my second sheet I'm using INDEX to pull the list of friends, and VLOOKUP to pull only their meal preference column. On this sheet, there's also a "notes" column for specific meal-related info.
• When a friend tells me they can't come, I delete their row from the first sheet. When I invite a new friend, I add a new row in the first sheet.
• After adding or deleting a row, the second sheet's INDEX and VLOOKUP columns update as expected - but the "notes" column is static, so its data is out of sync.

My question - how can I ensure that the data in the "notes" column stays in sync with the rest of the sheet?

I've uploaded the mini-sheet thing here, for what it's worth. What a cool function.

Thanks team!

Book1
	A	B	C	D
1	NAME	EMAIL	LOCATION	MEAL
2	Friend 1	email@place	London	Beef
3	Friend 2	thing@what	Paris	Chicken
4	Friend 3	hello@bla	New York	Pork
5	Friend 4	schmee@gp	Katoomba	Prawn
6	Friend 5	ex@mple	Bogota	Cabbage
Sheet1

Book1
	A	B	C
1	NAME	MEAL	NOTES
2	Friend 1	Beef
3	Friend 2	Chicken
4	Friend 3	Pork	Allergic to apple sauce
5	Friend 4	Prawn
6	Friend 5	Cabbage
Sheet2

Cell Formulas
Range	Formula
A2:A6	A2	=INDEX(Sheet1!A2:A6,,1)
B2:B6	B2	=VLOOKUP(A2,Sheet1!A2:D25,4,FALSE)
Dynamic array formulas.

 

[kvsrinivasamurthy]

It is better to add column (E) in Sheet1 and Vookup for column C in Sheet2.

 

[MaatWoodfriend]

Hi kvsrinivasamurthy! Thanks for the speedy reply.

I wish it were simply a matter of pulling everything from sheet 1, however I've got a specific need for a solution to my problem as posted.

The actual, real world thing I'm trying to do involves multiple workbooks across multiple servers with multiple users and uses - though rather than explain that whole sausage I just presented the problem in its simplest form. I really hope there's a straightforward solution out there... I'm all out of ideas.