Absoute Cell Ref Changes When Inserting New Row in Referenced Shee

  • Thread starter Thread starter jj
  • Start date Start date
J

jj

I have an application where I need to update cell B5 in Sheet 1 with a cell
value from a specific cell (C4) on Sheet 2. The issue is that in this
instance, I need to be able to select Row 4 in Sheet 2 and insert a new row
each month, so that I can record new data in Row 4 for the current month. I
thought that by making the cell reference in Sheet 1 absoute ($B$5) the
current value in Sheet 2, C4 would always be captured. In fact what happens
is that when I select Row 4, Sheet 2 and insert a new row, the $B$5,Sheet 1
cell reference changes to $B$6,Sheet 1.

How can I make sure that the cell reference in Sheet 1 remains absolute with
respect to capturing the value in C4, Sheet 2 when a new line in Sheet 2 is
inserted?

Thanks for any help you can provide.

John
 
I think you have a typo or two in your description but maybe this will help.

In B5 of Sheet1 enter =INDIRECT("Sheet2!C4")

Will always refer to Sheet1!C4


Gord Dibben MS Excel MVP
 
THANKS SO MUCH!!

Gord Dibben said:
I think you have a typo or two in your description but maybe this will help.

In B5 of Sheet1 enter =INDIRECT("Sheet2!C4")

Will always refer to Sheet1!C4


Gord Dibben MS Excel MVP



.
Click to expand...
 
Hi again. It didn't work... the dreaded #REF! error.

In the B5, Sheet 1 cell, I have =INDIRECT('Unit 0 Data'!A7). The A7 field is
formatted as a date, and displays MMM-YY for the entered value mm/dd/yyyy.

Also, both Sheet 1 and Sheet 2 are worksheets in the same workbook. This is
Excel 2002 on a Windows 2000 machine.

Thanks for any ideas.

John
 
Thanks, Gord. That got it. What are the significance of single quotes and
double quotes? In other words, how are they parsed by Excel?

Thanks for the education...

John
 
Single quotes are used when your sheet name has spaces.

The double quotes denote a string(text)

Indirect uses a string.

See INDIRECT help for much more on its use.


Gord
 
Back
Top