Excel Cell Add / Deletion problem?

Question

I'm currently experiencing problems where I have two sheets in excel, one sheet has a column of cells that are = to the other sheets.

My problem arises when I delete or add a row in that column, when I do so it automatically repoints the cell.

For example

Before

=Sheet1!A1
=Sheet1!A2
=Sheet1!A3

Afterwards once I delete the row that the second formula refers to.

=Sheet1!A1
=Sheet1!A3
=Sheet1!A4

I've tried placing a $ in the formula to no help.

Answer 1

The trick is to use named ranges.
Select all the cells in sheet2 by clicking the top left corner.
Click insert/name/define
Type 'sheet2', click add. If you do this correctly, then sheet2 will refer to '=Sheet2!$1:$65536'
Now go to sheet1. Enter the formula as =sheet2
Now go back to Sheet2. Do whatever changes.
Sheet1 will always get its referencing right.

Answer 2

You need to link your sheets or unlink your sheets prior to adding or deleting, to delete from one sheet and not the rest make sure your sheets are not linked, to add to all sheets with one add make sure there linked. When deleting from one sheet and not the rest make sure the sheet tab for the sheet your deleting from is the only one that is white, if more then one is white then it is linked. If you want to delete cell A2 from all the sheets make sure they are linked and it will update all sheets.

If you put your $ in front of A ex. $A it means it has to be in column A no matter what. If you put the $ in front of the row ex. A$1 it means it looks in row 1 no matter what. $A$1 means it has to be in cell A1 no matter what. Its called cell reference.

Answer 3

I tried your example in my Excel and after deleting Row 2 in the first sheet, I get a #REF! error in my 2nd sheet. It doesn't add an additional row. ????

Answer 4

I'd like to know the same

if you cut/past rather than deleting rows I think you don't get that (didn't try it) (maybe a workaround?)

you could use a macro as well

I know none of them are convenient, but maybe the will work for you