IF Formula

[vingo]

Here it is...

G11 corresponds to the sheet I want the cells to pull info off of...
M8 =INDIRECT(G11& "!BD23")
N8 =INDIRECT(G11& "!BD52")
O8 (oh8) =IF(ISERROR(N8+M8), "0", N8+M8)


If M8 = 5, N8 = 3, then O8 = 8
The problem is if either M8 or N8 is blank because the referenced cell is blank, O8 remains 0. Any may to make M8 and N8 show 0 if the referenced cell is blank or make O8 automatically calculate a blank in M8 or N8 as 0? This is complicated..All help would be appreciated.

 

[Barrie Davidson]

What about trying:

M8 =IF(INDIRECT(G11& "!BD23")="",0,INDIRECT(G11& "!BD23"))
N8 =IF(INDIRECT(G11& "!BD52") ="",0,INDIRECT(G11& "!BD52"))

Would that work for you?

 

[Tazguy37]

Would

=IF(N8*M8=0, "", N8+M8)

work in O8?

 

[vingo]

Thanks for trying everyone!! Unfortunately neither suggestions work. Any other suggestions??

 

[Scott Huish]

What exactly is not working? And what is generating the error you are trying to trap? Also, you should remove your quotes around the zero in your 08 formula.

 

[dr_shivan]

you can try the below

08=IF(ISERR(M8),0,M8)

or you can merge the above with M8 replacing M8 with your code.
This was, both M8 and N8 will give either a 0 or the result. no more #REF

Hope the above solve your problem

 

[dr_shivan]

oops didn't see your o8 was occupied, merge both the formula up would give you a clean original function.

 

[vingo]

I finally figured it out on my own!!! Here is what I got...what do you guys think??

M8 =IF(INDIRECT(G11 & "!BD23")="","0",INDIRECT(G11 & "!BD23"))
N8 =IF(INDIRECT(G11 & "!BD52")="","0",INDIRECT(G11 & "!BD52"))

 

[Aladin Akyurek]

I finally figured it out on my own!!! Here is what I got...what do you guys think??

M8 =IF(INDIRECT(G11 & "!BD23")="","0",INDIRECT(G11 & "!BD23"))
N8 =IF(INDIRECT(G11 & "!BD52")="","0",INDIRECT(G11 & "!BD52"))

A 0, which is a number, should not be put between double quotes. If the target cell is empty, you'll get automatically a 0, so there is no reason why you'd call INDIRECT twice. Doesn't

=INDIRECT("'"&G11&"'!BD23")

suffice? You can custom format the formula cell as:

[=0]"";General

if you want to.