after i save my workbook and reopen it a cell with an if statement says =#VALUE!

[bleeet]

everything works once i input the if formula in but once i close and reopen it =#VALUE! show up on the cell.

anyone happen to know what i am doing wrong?

here is the if statement in the cell

=+IF(E57="BRANCH PAYS:","*DEBITED BRANCH FOR FCT FEES OF $"&TEXT(F57,"0.00")&"",IF(E57="SWITCH-IN:","* SWITCH-IN PROMOTION CHARGED FOR FCT FEES OF $"&TEXT(F57,"0.00")&" AND DISCHARGED FEES",IF(E57="DEBT ACCOUNT:","*DEBITED CLIENT ACCOUNT FOR FCT FEES OF $"&TEXT(F57,"0.00")&"",IF(E57="SWITCH-IN BRANCH PAYS:","*SWITCH-IN CHARGED BRANCH FOR FCT FEES OF $"&TEXT(F57,"0.00")&" & DISCHARGED FEES, CLIENT CHARGED ADDITIONAL FCT FEES ",IF(AND(E57="SWITCH-IN ADDITIONAL FEES:",G54<0),"ADDITIONAL FCT FEES $"&TEXT(G60,"0.00")&" CHARGED TO CLIENT & SWITCH IN PROMO CHARGED FOR FCT FEES & DISCHARGE FEES",IF(E57="switch-in additional fees:","ADDITIONAL FCT FEES $"&TEXT(G60,"0.00")&" CHARGED TO CLIENT",IF(AND(E57="BRANCH PAYS ADDITIONAL FEES:",G54<0),"ADDITIONAL FCT FEES $"&TEXT(G60,"0.00")&" CHARGED TO BRANCH & SWITCH IN PROMO CHARGED FOR FCT FEES & DISCHARGE FEES",IF(E57="branch pays additional fees:","ADDITIONAL FCT FEES $"&TEXT(G60,"0.00")&" CHARGED TO BRANCH",""))))))))

 

[RSXchin]

Good lord that's a long formula... I'm sure we can shorten that (so to speak).

 

[bleeet]

I wish i knew how to shorten it

 

[RSXchin]

Are you okay with creating a custom function? Macros have to be enabled for that though.

 

[bleeet]

yea i have some macros in the workbook

 

[bleeet]

Good lord that's a long formula... I'm sure we can shorten that (so to speak).

is the reason why i am getting that value error because the formula is too long?

 

[RSXchin]

see if this does it for you

Function Who_Pays_What()
    Dim Amount As Double, Amount2 As Double
    Amount = Format(Range("F57"), "$0.00")
    Amount2 = Format(Range("G60"), "$0.00")
    Who_Pays_What = "Unknown error"
    Select Case True
        Case Range("E57") = "BRANCH PAYS:"
            Who_Pays_What = "*DEBITED BRANCH FOR FCT FEES OF " & Amount & """"
            
        Case Range("E57") = "SWITCH-IN:"
            Who_Pays_What = "* SWITCH-IN PROMOTION CHARGED FOR FCT FEES OF " & Amount & " AND DISCHARGED FEES"
            
        Case Range("E57") = "DEBT ACCOUNT:"
            Who_Pays_What = "*DEBITED CLIENT ACCOUNT FOR FCT FEES OF " & Amount & """"
                    
        Case Range("E57") = "SWITCH-IN ADDITIONAL FEES:" And Range("g54") < 0
            Who_Pays_What = "ADDITIONAL FCT FEES " & Amount2 & " CHARGED TO CLIENT & SWITCH IN PROMO CHARGED FOR FCT FEES & DISCHARGE FEES"
        
        Case Range("E57") = "SWITCH-IN BRANCH PAYS:"
            Who_Pays_What = "*SWITCH-IN CHARGED BRANCH FOR FCT FEES OF " & Amount & " & DISCHARGED FEES, CLIENT CHARGED ADDITIONAL FCT FEES "
        
        Case Range("E57") = "BRANCH PAYS ADDITIONAL FEES:" And Range("g54") < 0
            Who_Pays_What = "ADDITIONAL FCT FEES " & Amount2 & " CHARGED TO BRANCH & SWITCH IN PROMO CHARGED FOR FCT FEES & DISCHARGE FEES"""
            
        Case Range("E57") = "BRANCH PAYS ADDITIONAL FEES:"
            Who_Pays_What = "ADDITIONAL FCT FEES " & Amount2 & " CHARGED TO BRANCH"
    End Select
End Function

now you can just use

=Who_Pays_What()

maybe that'll make it better.

 

[bleeet]

sorry i am not good at excel where do i put this formula

in a module?

 

[RSXchin]

Yes, create a module. NOT in the ones that say ThisWorkbook or Sheet1 blah blah blah.

 

[bleeet]

Yes, create a module. NOT in the ones that say ThisWorkbook or Sheet1 blah blah blah.

sorry i can't figure out how to input in cell A69