Absoute Cell Ref Changes When Inserting New Row in Referenced Shee
 

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

Absoute Cell Ref Changes When Inserting New Row in Referenced Shee
 

This will *always* refer to cell B5:

=INDIRECT("B5")

--
Biff
Microsoft Excel MVP


"jj" wrote in message
...
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

Absoute Cell Ref Changes When Inserting New Row in Referenced Shee
 

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

On Thu, 1 Apr 2010 10:45:02 -0700, jj wrote:

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

Absoute Cell Ref Changes When Inserting New Row in Referenced
 

THANKS SO MUCH!!

"Gord Dibben" wrote:

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

On Thu, 1 Apr 2010 10:45:02 -0700, jj wrote:

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

.

Absoute Cell Ref Changes When Inserting New Row in Referenced
 

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" wrote:

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

On Thu, 1 Apr 2010 10:45:02 -0700, jj wrote:

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

.

Absoute Cell Ref Changes When Inserting New Row in Referenced
 

You missed the double quotes.

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


Gord

On Thu, 1 Apr 2010 12:18:05 -0700, jj wrote:

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" wrote:

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

On Thu, 1 Apr 2010 10:45:02 -0700, jj wrote:

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

.

Absoute Cell Ref Changes When Inserting New Row in Referenced
 

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" wrote:

You missed the double quotes.

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


Gord

On Thu, 1 Apr 2010 12:18:05 -0700, jj wrote:

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" wrote:

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

On Thu, 1 Apr 2010 10:45:02 -0700, jj wrote:

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

.


.

Absoute Cell Ref Changes When Inserting New Row in Referenced
 

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

On Thu, 1 Apr 2010 13:32:01 -0700, jj wrote:

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" wrote:

You missed the double quotes.

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


Gord

On Thu, 1 Apr 2010 12:18:05 -0700, jj wrote:

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" wrote:

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

On Thu, 1 Apr 2010 10:45:02 -0700, jj wrote:

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

.


.

Absoute Cell Ref Changes When Inserting New Row in Referenced
 

Perfect. It worked. Thanks again.

"Gord Dibben" wrote:

You missed the double quotes.

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


Gord

On Thu, 1 Apr 2010 12:18:05 -0700, jj wrote:

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" wrote:

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

On Thu, 1 Apr 2010 10:45:02 -0700, jj wrote:

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

.


.