As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

Excel question - cell references

DrezDrez Registered User regular
edited August 2012 in Help / Advice Forum
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.

Switch: SW-7690-2320-9238Steam/PSN/Xbox: Drezdar
Drez on

Posts

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

    steam_sig.png
    Origin: KafkaAU B-Net: Kafka#1778
  • ecco the dolphinecco the dolphin Registered User regular
    edited August 2012
    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.

    ecco the dolphin on
    Penny Arcade Developers at PADev.net.
  • DrezDrez Registered User regular
    Eh, shit, INDIRECT - of course. Thank you!

    Switch: SW-7690-2320-9238Steam/PSN/Xbox: Drezdar
Sign In or Register to comment.