Linked cells return #Ref when they shouldn't!
 

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.

Linked cells return #Ref when they shouldn't!
 

Hi

You have to use absolute references in SheetB:

=SheetA!$A$5

Regards,
Per

"liiza" skrev i meddelelsen
...
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.

Linked cells return #Ref when they shouldn't!
 

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!

--------------------
(Ms-Exl-Learner)
--------------------


"liiza" wrote:

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.