Linked cells return #Ref when they shouldn't!

  • Thread starter Thread starter liiza
  • Start date Start date
L

liiza

Excel 2007 Workbook 1 has Sheet A and Sheet B.

Sheet B is linked to Sheet a by clicking on the row in Sheet A and Pasting
Link on row i want in Sheet B.

=SheetA!A5
=SheetA!B5
Etc. right across the row.

I have about 6000 lines like this.

If i delete a line on Sheet A, Sheet B correspondingly changes to #REF, I
cannot make it update to what is now that line on Sheet A. I was thinking if
I deleted say Row 5 on Sheet A that Sheet B would automatically change the
corresponding row to Row 6 rather than #Ref
am I mistaken?
Was sure I done it that way before.
 
If you reference your formula like =SheetA!A5 then once the SheetA A5 cell is
deleted then you will get error like this #REF! (=SheetA!#REF!), So use
indirect function in your formula like the below:-
=INDIRECT("SheetA!A5")
In this case if you delete the SheetA A5 cell you will not get #REF! error.

Remember to Click Yes, if this post helps!
 
Back
Top