MrExcel Publishing
Your One Stop for Excel Tips & Solutions

#NUM! and cell references


Posted by Bill on January 13, 2000 6:47 PM

Excel97.
I've had to modify this sheet to remove a column or two.
Of course, I messed up all the cell references in sheet two which pulls all it's information from cell one that I modified. I thought I could just fix the references, and everything would be fine. It's not. The following is the formula in sheet two that pulls from "RFA" (sheet one).=SUM(IF(RFA!$G$4:$G$500=$A37,IF(RFA!$H$4:$H$500=F$36,1,0),0))
It's an array formula, and yes, I had the { } brackets around it---they just didn't copy to notepad.
So now in sheet two, I get #NUM! response, even though the cell references appear to be correct. Does this have anything to do with how I formatted the cells in sheet one? (When I deleted the unnecessary column, the other columns were shifted left).
Any tips would be greatly appreciated.
Bill


Posted by Thomas Venn on January 19, 2000 10:13 AM

I am not very familiar with ARRAY formulas, but one way to "circumvent" your problem is to NOT delet any cells, rather, do a CUT and PASTE instead.

Cheers,

Thomas