Absolute references aren't absolute?

[Sir Spike]

Hello. I have a small problem with excel.

I have two worksheets in one workbook. I want the second one to always reference cell B2 in the first, regardless of the changes I make to the first worksheet.

The problem I have is this: I absolute reference cell B2, but if I insert a new row between row 1 and 2, the reference changes to cell B3! I don't want the reference to change, I want it to look at cell B2. Always. Regardless of any changes I make to the worksheet.

To elaborate further: The first worksheet that is being referenced is always changing. The second worksheet is always fixed. Whenever I make changes to the first worksheet, the reference changes to compensate any change. I don't want it to do this. I want it to always point at B2!

Why does excel have to be so damn smart! I don't want it to try and compensate for changes! I want it to point at B2! Is there a 'dumb reference' function???

[davesexcel]

Hi there ..
How do you absolute the reference??

[Sir Spike]

I assume you are asking me what my understanding of absolute referencing a cell means?

I'm using the dollar signs: $B$2

Doesn't make any difference though.

I'm not changing the position of the cell with the reference formula; I'm changing the position of the cell that is being referenced.

I did a search for 'absolute reference' and found this: http://www.excelforum.com/showthread...lute+reference

Which says you can use the INDIRECT function, but I can't figure out how to use it.

[davesexcel]

what range referers to cell(2,2) always??

[Sir Spike]

I figured it out using the INDIRECT function:

=INDIRECT("Sheet1!"&"B2")

Problem solved!

I wish this was more obvious though. Had to do a lot of searching and playing around to make this work.

Excel should have a 'Dumb Reference' or 'Fixed Reference' function, as this is effectively what I have achieved with the INDIRECT formula above.

Thanks for your help anyway...

[davesexcel]

I figured it out using the INDIRECT function:

=INDIRECT("Sheet1!"&"B2")

Problem solved!

I wish this was more obvious though. Had to do a lot of searching and playing around to make this work.

Excel should have a 'Dumb Reference' or 'Fixed Reference' function, as this is effectively what I have achieved with the INDIRECT formula above.

Thanks for your help anyway...

Great,
then the problem has been solved??