super absolute cell reference

[asylum]

Hi, fairly simple(?) this..

I have a fomula in cell a1 in sheet2 that says =sheet1!a1

BUT every month I insert a column to the left of column a in sheet 1. this rather annoyingly automatically changes the formula in sheet2 to =sheet1!b2 but I want it NOT to do this, and to remain as referring to the original cell (with its new data)

how can i achieve this (and yes, i do still want to add a column to the left each month, so cannot change the process).

Thanks

Andy

 

[Jonmo1]

If it's only once a month that you insert the column...

On sheet 2 that holds the formula(s)
use Edit - Replace
Replace What: Equal Sign
Replace With: #= (Pound Sign Equal sign)
Replace All

Insert your column.

Reverse the replace action
Replace What: #=
Replace With: =
Replace All

Hope that helps.

 

[asylum]

interesting work around thanks, but It does somewhat rely on remembering to do it, for a small number of cells for a very large and complex workbook (which to be fair i did not describe above). really i need some fomula that excel wont change..

Thanks

 

[Jonmo1]

interesting work around thanks, but It does somewhat rely on remembering to do it, for a small number of cells for a very large and complex workbook (which to be fair i did not describe above). really i need some fomula that excel wont change..

Thanks

You already rely on remembering to do it...when you insert the column.

 

[Expiry]

You could try INDIRECT

=INDIRECT("sheet1!a1")

 

[asylum]

Doh! Thanks Expiry that is excatly what I'm after! - top marks,

Cheers

Andy

 

[T. Valko]

Hi, fairly simple(?) this..

I have a fomula in cell a1 in sheet2 that says =sheet1!a1

BUT every month I insert a column to the left of column a in sheet 1. this rather annoyingly automatically changes the formula in sheet2 to =sheet1!b2 but I want it NOT to do this, and to remain as referring to the original cell (with its new data)

how can i achieve this (and yes, i do still want to add a column to the left each month, so cannot change the process).

Thanks

Andy

Here's another one that doesn't use a volatile function:

=INDEX(Sheet1!1:1,1)