Nested If function with multiple criteria

[keyservices]

Can someone help me resolve an If formula in excel 2010 please?  I have a workbook with multiple worksheets.  I'm trying to copy the cell value that is not blank from one worksheet called Payment Calc to another with my If statement.  My formula in the destination cell reads;

=IF('Payment Calc'!B12<>"",'Payment Calc'!B12,IF('Payment Calc'!B27<>"",'Payment Calc'!B27,IF('Payment Calc'!D19<>"",'Payment Calc'!D19,'Payment Calc'!D33))). 

Unfortunately, when B12, B27 and D19 are all blank, D33 will not populate in the cell with the formula.  The first three cells work fine when one of them has a value.  I've researched the problem accross multiple resources and have been unable to resolve on my own.  Thanks in advance if someone can help with a solution.

[#2]

Hi

the formula appears to work fine when I replicate it.

When you say the other cells are "blank" can you check by doing

=ISBLANK(B12)

=ISBLANK(B27)

= ISBLANK(D19)

They should all show TRUE if truly blank

[#3]

They are all have formulas and they all read as False.  That said, the first three conditions do result in their values being copied into the destination cell when they have data.  For some reason the final condition will not populate even if I adjust the formula.  So strange. 

I've attached my workbook in hopes you can replicate the issue.  The destination cell with the If statement in question is in D10 on the Credit Report tab.  The Payment Calc tab has the four cells the If statement is targeting.  Two of the cells have imbedded If formulas and the other two have imbedded Max formulas.

[#4]

Hi,

In your file in formula for Credit Report D10 is just D33 if all blanks. It shall be 'Payment Calc'!D33 - as in your first message

[#5]

You're right Sergei... Thanks for pointing that out.  I did that this morning before I cleaned up the excel to attach to this forum.  Unfortunately that wasn't the issue and the problem persists as you can probably see.  Have you tested it with the corrected 'Payment Calc'!D33 statement too?  It doesn't seem to work for me regardless of the last argument placed in the formula.  :(

Thanks for trying Sergei. 

[#6]

Nope, i didn't test - your workbook is protected. But i'll try on my own file with simular formula bit later. In general it shall work

[#7 · Accepted solution]

I guess the issue is with D19 cell. MAX() of three blank cells (with "") returns 0 (zero), not blank. Perhaps you have custom format for the cell and it shows you empty cell, not blank.

I copied your D19 value into another workbook, it gives zero.

[#8]

You are a GENIUS Sergei!  Thank you very much!!

When I adjusted my formula to look for D9 instead of D19, and copy D19 if D9 was not empty, the problem was solved.

Thank you so very much!!!  :)

[#9]

You are welcome, no problem