excel absolute reference

[matrixindicator]

For excel experienced users this should be an easy question ?

On my first Sheet I make a reference to my second sheet. Absolute to the first cell. This works.

=Sheet2!$A$1

When I insert a row on the first line, the value of my first row is placed A2.

My reference is also automatic moving to the second row

=Sheet2!$A$2

However I like that my reference should remain fixed on the first cell $A$1 How can I do this.

 

[GlennUK]

One way to do this is to use the INDIRECT function, like this:

=INDIRECT("Sheet2!A"&ROW())

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[matrixindicator]

Glenn, thanks

I don't see why its works, but it works !

 

[xlbo]

read the help on the INDIRECT funtion - it converts text to a range reference. Pure range references, whether abolute or not, will be incremented by insert / delete / cut. A text string will not. That is why INDIRECT works in this scenario

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

read the help on the INDIRECT funtion - it converts text to a range reference. Pure range references, whether abolute or not, will be incremented by insert / delete / cut. A text string will not. That is why INDIRECT works in this scenario

yes Geoff, couldn't have put it better myself.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.