Stop cell reference changing when insert column

**freud1** · 06-04-2009, 04:48 AM

Hi Guys,

Probabilly really simple,

I have referenced data in two colums on a sheet A and B,

Column A contains the latest data, each month i insert new column (moving column A to column B)

However all of my references continue to follow the original data (eg will change from column A to column B)

this happens despite using Absolute references. (=$A$1)

Is there a way to lock these cell references to only ever display column A etc?

**DonkeyOte** · 06-04-2009, 04:58 AM

You can use INDEX:

=INDEX(1:65536,1,1)

or INDIRECT

=INDIRECT("A1")

The former being non volatile would be preferable IMO.

**freud1** · 06-04-2009, 06:46 AM

thanks Donkyote,

I've been avoiding the Index Function for years now so i guess it'a time to bite the bullet...

many thanks

**gurumagic** · 07-27-2012, 10:44 AM

Brilliant! This is exactly what I was looking for. My formula is about 12 feet long now, but it does exactly what i want! No if I can only get the conditional formatting to not move into the newly inserted column.