Delete ROW on Sheet#1 corupts data on Sheet#2

[Duane]

If I delete a ROW od data on sheet1, my formulas on sheet2 screwup.
a2 on sheet2: (Filled down to 200)
=If('LIST'!$B2="Y","YES","NO")
If I delete row #25 on sheet1, my row 25 on sheet2 has a #REF error

Any ideas how I overcome this?
Thanks

 

[Glenn]

If I delete a ROW od data on sheet1, my formulas on sheet2 screwup.
a2 on sheet2: (Filled down to 200)
=If('LIST'!$B2="Y","YES","NO")
If I delete row #25 on sheet1, my row 25 on sheet2 has a #REF error

Any ideas how I overcome this?
Thanks

By chance, is sheet1 really named "LIST"?

Maybe try this in "sheet2":

=IF(INDIRECT("'LIST'!$B"&ROW())="Y","YES","NO")

 

[Daryl S]

Duane -

If you delete a row in the first sheet, then any cell in the second sheet
referencing that cell loses the reference (hence the #REF). You can delete
the CONTENTS of the cells in the row instead of deleting the row, and the
references will remain.

Since you didn't give us any information on the relationships between the
two sheets or what you would like to happen, we can't recommend a potentially
better solution.

 

[Duane]

Thanks! That works excatly like I was trying to do!
Now if I can only figure out why INDEX keeps looking at a cell on the sheet
I have the formula on and NOT the sheet where I want the info from!!!

=INDEX(LIST!F:F,F2) looks at F2 on this sheet, not LIST sheet
=INDEX(LIST!F:F,) appears to give me the right answer but I have more
reading to do on INDEX to understand why it works!!

Thanks again guys, great work!!!

 

[Gord Dibben]

Awful waste of my time and effort replying to your original post on same
subject.

Remember Kub Kar List question?


Gord Dibben MS Excel MVP