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).
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,
Would you like to say thanks? Please click the: "Add Reputation" button, on the grey bar below the post.
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.
Either of the Chemist's formulas will work. Don't miss the equal "=" sign in the INDEX formula though.
Last edited by ConneXionLost; 12-11-2009 at 05:24 PM.
I'll try it. Thanks for the quick responses! Have a great weekend.
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 thiswith INDIRECT
if((INDEX($1:$5000,ROW(),1)+INDEX($1:$5000,ROW(),2)++INDEX($1:$5000,ROW(),3))=0,"true","false")=if((INDIRECT("A"&ROW())+INDIRECT("B"&ROW())+INDIRECT("C"&ROW()))=0,"true","false")
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks