In sheet: A,
instead of using this simple link formula in say B2: =B!A1,
then copying B2 across/filling down to cover the desired source area in B to
be linked
use this in B2:
=OFFSET(INDIRECT("'B'!A1"),ROWS($1:1)-1,COLUMNS($A:A)-1)
It returns the same link functionality, can be copied across/filled down,
BUT it will always point to the "fixed" source area linked in B, resistant to
deletions of rows in the source sheet. Try it out and prove to yourself that
it works exactly as you seek here. Inspiring? hit the YES below
--
Max
Singapore
---
"BMP" wrote:
> I am using two worksheets (in the same file), each containing a vertical list
> of names. Worksheet B pulls a list of these names from from Worksheet A
> using "=worksheetA!cell# At times I must delete a row containing a name from
> Worksheet A. I thought this would delete the data in Worksheet B and move
> up all the new data in Worksheet A into Worksheet B since I deleted a row and
> all the names below this row moved up to a new cell location. I have also
> used the "$" function in my formula, but no luck...........as everytime I
> delete a row in Worksheet A, the copied cell from A that is in B changes to
> #REF. It seems that Worksheet B is not recognizing that all the data in A
> has moved up one row (via the deleted row).
>
> Any suggestions ?
|
Similar Threads
