If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Deleting Row
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 ? |
#2
|
|||
|
|||
Deleting Row
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 ? |
Thread Tools | |
Display Modes | |
|
|