Help with this formula PLEASE

[Mister H]

Hi All:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have a code that names some cells and then places an IF statement in another cell to add up the 3 named cells This works fine when I have all 3 cells on the spreadsheet but there are not always IDENTIFIED or UNAPPLIED. If one of these are missing I get #NAME? in the cell containing the formula. Here is what I have:<o></o>
<o></o>

 <o:p></o:p>

[COLOR=black][FONT=Verdana]ActiveCell.Offset(3, 7).FormulaR1C1 = "=IF(Applied+Unapplied+Unidentified-Totals<>0,""Not Balanced"",""Balanced"")"<o:p></o:p>[/FONT][/COLOR]

<o></o>
<o></o>
Hopefully I have explained well enough that someone understands...

Bye 4 Now,
Mark

 

[Mister H]

Hi:

Just to explain a little further the formula works fine if all 3 named ranges (cells) are present in the spreadsheet (Applied, Applied and Unidentified) however if one of the named ranges is missing then the IF formula returns the result #NAME?

If someone can tell me if it is or isn't possible to have a formula with a missing Named range in it that would be a step in the right direction.

Maybe I will need to alter my original code that names the ranges to insert all 3 ranges even if the number is zero.

THANKS for looking,
Mark

 

[Mister H]

Good Morning ALL

Just bumping this in hopes of someone on the morning crew can give me direction.

Can a named Range (single cell) be included in a formula even if it doesn't exist on the sheet? Is there a way of writing the formula to say if the for example the Range "Unidentifed" does not exist on the sheet just Add zero?

IF(Applied+Unapplied+Unidentified-Totals<>0,""Not Balanced"",""Balanced"")


THANKS,
Mark

 

[Michael M]

Maybe something like

"=IF(ISERROR(Applied+Unapplied+Unidentified-Totals<>0),"""",IF(Applied+Unapplied+Unidentified-Totals<>0,""Not Balanced"",""Balanced""))"

BTW...it's the night shift here ...LOL !!

 

[Mister H]

THANKS for the reply Michael M.

Ya I guess my Greeting was a lttle TOO generic

THANKS for looking anyway

I tried your suggestion and it eliminated the #Name? but it did not place the formula there. Is there any way of makig a formula that says:

IF(ISERROR(Applied+Unapplied+Unidentified-Totals<>0) then try
Applied+Unapplied-Totals<>0,""Not Balanced"",""Balanced""))"

However if there is an error there then have it Blank

I tried this but I haven't got a clue about MOST things

"=IF(ISERROR(Applied+Unapplied+Unidentified-Totals<>0)," =IF(ISERROR(Applied+Unapplied-Totals<>0""",IF(Applied+Unapplied+Unidentified-Totals<>0,""Not Balanced"",""Balanced""))"

THANKS Again,
Mark

 

[moonfish]

What about something like,

=IFERROR(Applied,0)+IFERROR(Unapplied,0)+IFERROR(Unidentified,0)-IFERROR(Totals,0)

If you're not using excel 2007+ you'll have to replace each instance of iferror(x,0) with if(iserror(x),0,x)

 

[Mister H]

THANKS moonfish

I am using Excel 2003. I am not sure that I understand what you are saying I have to change is it:

=IF(ISERROR(Applied),0,Applied) etc

THANKS,
Mark

 

[Mister H]

Hi All:

I think I have it figured out. THANKS to ALL that assisted. Here is the formula that SEEMS to be working:

=IF(IF(ISERROR(Applied),0,Applied)+IF(ISERROR(Unapplied),0,Unapplied)+IF(ISERROR(Unidentified),0,Unidentified) -Totals<>0,""Not Balanced"",""Balanced"")

ActiveCell.Offset(3, 7).FormulaR1C1 = "=IF(IF(ISERROR(Applied),0,Applied)+IF(ISERROR(Unapplied),0,Unapplied)+IF(ISERROR(Unidentified),0,Unidentified) -Totals<>0,""Not Balanced"",""Balanced"")"

THANKS Again for everyones help. If anyone notices a problem with the formula (or one that I might run into) please do not hesitate to let me know...

Have a GREAT day,
Mark