Excel question - cell references

Drez

Is there a way to prevent Excel from updating absolute references when you delete columns/rows/whatever?

For example: I have data in columns CP, CQ, CR, CS.

In another column, I have a formula which references column CP: =IF($CP4=1, "Blah blah blah", "Bloo bloo bloo")

I want to make four copies of this worksheet and delete the other three columns that aren't pertinent to that data set. Meaning, I want to delete CP, have CQ become CP, and have my formula reference this new column.

I should note, I already solved to what I'm trying to do a different way, so this is just academic at this point - I can't seem to figure out if there's a way to prevent Excel from calculating new references based on delete/insert events. I thought there was, but I can't find the solution. I'm using 2007/2010.

KafkaAU

Pretty sure you can't turn off the auto-rereferencing. I just copy the formula and paste it back in after the event.

ecco the dolphin

Try using INDIRECT.

e.g.

=IF( INDIRECT("CP4") = 1, "1", "Not 1" )

Edit:

This works by treating converting the string "CP4" into the cell reference. Because "CP4" is a considered a string, not a cell reference, inserting/deleting rows/columns do not affect it.

You can also try

INDIRECT( "CP" & ROW() ) to make the string change based on row.

Edit2:

I suppose, for completeness, I should also mention the ADDRESS() function, which can be used to generate cell addresses easily.

Drez

Eh, shit, INDIRECT - of course. Thank you!