Excel Formula Error when deleting a column

**GlenChambers** · 12-11-2009, 04:38 PM

Hi,

This is my first time on this forum. Any help would be much appreciated.

Let's say I have the formula =if((a1+b1+c1)=0,"true","false"). In the scenario I delete column c, I get a reference error. Is there anyways around this? Can I alternate the formula so that excel won't give me the error message.

This also happens when I insert a new column after column B, which now becomes column C, and delete Column D (which was previously column C).

**ConneXionLost** · 12-11-2009, 04:48 PM

Hi GlenChambers,

Welcome to the forum!

Answering your question depends on where the data is kept in your spreadsheet.

Lets assume that in cell C1, you had the number "42".

If you delete column C, then you also delete the 42. Where should the formula now look to find the 42? Is it in a different cell?

Cheers,

**ChemistB** · 12-11-2009, 05:01 PM

Yes, there are ways around it. It depends on what you are trying to accomplish. If you are just trying to clean up your worksheet, you want to convert your formulas to values so that deleting A,B, or C doesn't give you the #REF.

To do that, simply copy Column D and Paste Special > Values.

If you want to insert or delete columns but you still want your formulas to point to columns A,B and C, then your formula needs to be a little lengthier. You can use INDIRECT or INDEX. I prefer INDEX because it's nonvolatile. Your formula would look something like this

Please Login or Register  to view this content.

with INDIRECT

Please Login or Register  to view this content.

**GlenChambers** · 12-11-2009, 05:08 PM

Lets say cell a1 = 5, cell b1 = 4 and cell c1 = 42 & cell d1 contained my “if statement” formula.

Let’s also suppose I inserted a new column between column “b” and “c” and inputted the number 42 in the newly created cell c1.

So now a1 = 5, b1 = 4, c1 = 42, d1 = 42, and cell e1 contains the "if statement" formula. So if I decide to delete cell d1 (originally cell c1) I get the reference error.

I hope I explained that clearly. Thanks again in advance.