Deleting data from Sheet 1 Column 1 also deleting data from Column 1 Sheet 2 and corrosponding rows on Sheet 2

Not sure if a am phrasing the question correctly basically I need to add or delete the list of names in Column 1 on Sheet 2 based on if names in Column 1 of Sheet 1 are added or deleted. I have the formula in Column 2 Sheet 2 for this as =Sheet1!A2:A96. I then need the corresponding rows of data next to Column 1 Sheet 2 to also delete (excel is good about auto adding). I have attached a sample sheet. So if name20 is deleted on Sheet 1 I need name20 to delete on Sheet 2 and all of the data in the row to the right of name20 to also delete.

sample.xlsx122.23 KB

By simply deleting the row

By simply deleting the row contains name20 in sheet 1 you can do this. It will delete the entry of name20 from sheet 2

Almir's picture

Deleting data from Sheet 1 Column 1 also deleting data from Colu

Problem occurs when you add or especially delete rows on sheet1. I recommend creating pivot table based on sheet1 column A.
1. First, on sheet 1 convert range to table. Thus, new rows added will be reflected on sheet2.
2. Create pivot table based on column A on sheet1. Use only Names as Row field.
3. Move that pivot table instead Column A (Names) on sheet 2.
4. When you delete or add rows on sheet1, just refresh Pivot table.
5. Formulas on the right will be updated accordingly.