Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!
Excel question - cell references
Drez
Registered User regular
Registered User regular
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.
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.
Posts
Origin: KafkaAU
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.