Deleting Row

B

BMP

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 ?
 
M

Max

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top