Absoute Cell Ref Changes When Inserting New Row in Referenced Shee

[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

 

[T. Valko]

This will *always* refer to cell B5:

=INDIRECT("B5")

 

[Gord Dibben]

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

 

[jj]

THANKS SO MUCH!!

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



.

 

[jj]

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

 

[Gord Dibben]

You missed the double quotes.

=INDIRECT("'Unit 0 Data'!A7")


Gord

 

[jj]

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

 

[Gord Dibben]

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

 

[jj]

Perfect. It worked. Thanks again.

You missed the double quotes.

=INDIRECT("'Unit 0 Data'!A7")


Gord



.