freeze cell reference when inserting a row

R

Rod

I have two worsheets A & B. I have linked cell D4 in A to D2 in B. When I
insert a row into B, I would like cell D4 in A to always display the
contents of D2 regardless of the number of rows i insert at that point. Can
anyone help me?
 
B

Bernie Deitrick

Rod,

=INDIRECT("'Sheet B'!D2")

Fix up the sheet name to reflect your actual names...

HTH,
Bernie
MS Excel MVP
 
R

Rod

Bernie,

Thanks for the help. It is quite working out for me though. I put in the
formula like you said ,but it returns "#ref" instead of what I want it to?
The other worksheet was open at the time so that shouldn't be the issue.
What do they mean by A1 reference, or R1C1? Does that have anything to do
with my problem?

Thanks in advance for you help.

Rod
 
B

Bernie Deitrick

Rod,

Worksheets are tabs within a workbook. Separate files are called workbooks, not worksheets... thus
our confusion.

You need to create a valid formula string within the INDIRECT function that refers to the cell of
interest. Open both books, type = into cell D4 of book A, then navigate to cell D2 of book B and
press Enter. You should then get a formula like

='[BookB.xls]Sheet 1'!$D$2

Which you can then include in the INDIRECT function by adding

INDIRECT("

between the = and the first ' (or, if there is no single quote mark, the square bracket [ ) and
then adding

")

at the end.


HTH,
Bernie
MS Excel MVP
 
R

Rod

I have been trying it both in separate worksheets and workbooks to see which
one would work out the best. I will try your suggestion and let you know the
results

Bernie Deitrick said:
Rod,

Worksheets are tabs within a workbook. Separate files are called workbooks, not worksheets... thus
our confusion.

You need to create a valid formula string within the INDIRECT function that refers to the cell of
interest. Open both books, type = into cell D4 of book A, then navigate to cell D2 of book B and
press Enter. You should then get a formula like

='[BookB.xls]Sheet 1'!$D$2

Which you can then include in the INDIRECT function by adding

INDIRECT("

between the = and the first ' (or, if there is no single quote mark, the square bracket [ ) and
then adding

")

at the end.


HTH,
Bernie
MS Excel MVP


Rod said:
Bernie,

Thanks for the help. It is quite working out for me though. I put in the
formula like you said ,but it returns "#ref" instead of what I want it to?
The other worksheet was open at the time so that shouldn't be the issue.
What do they mean by A1 reference, or R1C1? Does that have anything to do
with my problem?

Thanks in advance for you help.

Rod
Click to expand...
Click to expand...
 
R

Rod

Thank you so much Bernie. It was the quotation marks I was lacking. What is
their purpose? What does that say to the computer?
 
B

Bernie Deitrick

The quote marks tell Excel that the value being passed to the function is a string, which is what
the INDIRECT function requires.

HTH,
Bernie
MS Excel MVP
 
Top