A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Deleting Row



 
 
Thread Tools Display Modes
  #1  
Old April 18th, 2010, 11:17 PM posted to microsoft.public.excel.worksheet.functions
BMP
external usenet poster
 
Posts: 1
Default 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  
Old April 19th, 2010, 12:12 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.