PDA

View Full Version : PING: Excel People

Suburban Rhythm
10-19-2010, 12:02 PM
Not sure where I am going wrong on this-

Have a ss with 13 tabs, a Total and each month.

Each month will list a name and attendance, with multiple codes. At the end of the row is a sum of each possible code.

The Total page will be a running total, year to date, of the codes.

Here is the issue I am running into. As names are added (say a new name in October, not used from Jan-Sept), a row is inserted in the month tab and total tab.

I'd like to keep the formulas consistent throughout the Total tab, therefore for each name, it will look to all months. In the instances where a new name is added, I am getting an error (as expected) since that name isn't found in some months.

What formula can I use to eliminate the error, so that if the name doesn't exist for some months, I insert a "0", then continuing summing with the months I do get a value?

I've tried =IF(ISNA(VLOOKUP........)),"0",VLOOKUP(......)), no dice.
Same for ISERR and ISERROR.

Where am I going wrong?
FrogMan
10-19-2010, 12:06 PM
have you tried putting the "0" as a simple 0 (without the ""). I think it might be taking your "0" as a text value and then might not be able to add it up, causing the error...

FM
FrogMan
10-19-2010, 12:10 PM
dola, otherwise, I don't see what might be the error as I always use the ISERROR function do make sure I don't screw up any formulas...

FM
Suburban Rhythm
10-19-2010, 12:11 PM
Gold, that was it!

I knew FOFC wouldn't fail me.

Thanks
Passacaglia
10-19-2010, 12:13 PM
I generally do my if statement the opposite way:

IF(VLOOKUP(......),VLOOKUP(.......),0)

Dunno if that will help, but that way you don't have to use the ISNA function (never used that).

EDIT: Never mind!
FrogMan
10-19-2010, 12:17 PM
good! :)

FM
Suburban Rhythm
10-19-2010, 12:19 PM
I generally do my if statement the opposite way:

IF(VLOOKUP(......),VLOOKUP(.......),0)

Dunno if that will help, but that way you don't have to use the ISNA function (never used that).

EDIT: Never mind!

I think I need the additional one in there (in this case ISERROR was correct, started with ISNA) because I would get an error when the TOTAL tab was looking to January and Joe Schmoe doesn't have any data in that month.

Or I just like making extra work for myself.

;)
FrogMan
10-19-2010, 12:20 PM
might be easier (i.e. shorter in the formula) for you to use a SUMIF function. One per month, then you can skip the IF check because if excel doesn't find it in one tab, it will only sum up to zero...

Check the excel help if you've never used the SUMIF function.

FM