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.
| Attachment | Size |
|---|---|
| sample.xlsx | 122.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
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.