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]

after you create the module, and copied that code into the module, go to A69 and type

=Who_Pays_What()

It's its own formula.

 

[bleeet]

yea i did that

it now says #value! in the cell

i don't know what i did wrong


btw thank you so much for the help

 

[bleeet]

e57 is a pull down menu if that is the problem

 

[RSXchin]

it happens when you have empty f57 or g60. Here is an updated version

Function Who_Pays_What()
    Dim Amount As String, Amount2 As String
    If Range("F57") = "" Then
         Who_Pays_What = "F57 empty"
         Exit Function
    ElseIf Range("G60") = "" Then
        Who_Pays_What = "G60 empty"
        Exit Function
    End If
    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
Function Between_Ands(ByVal Full_String As String, ByVal First_Delimiter As String, ByVal Second_Delimiter As String) As String
Pos = InStr(Full_String, First_Delimiter)
pos2 = InStr(Full_String, Second_Delimiter)
If Pos = 0 Or pos2 = 0 Then
    Between_Ands = "Missing Delimiter"
    Exit Function
End If
Between_Ands = Mid(Full_String, Pos + Len(First_Delimiter), pos2 - (Pos + Len(First_Delimiter)))
End Function

 

[Peter_SSs]

If you wanted to stick with standard functions, you could consider something like this. I know I don't have the final result in the correct cell, but I'm just trying to demonstrate a method. You can change the layout to suit if you think you want to use this idea.

Make a table like I have in N47:P54
Then try the formula I have in L57

Excel Workbook
	E	F	G	H	I	J	K	L	M	N	O	P
47										ADDITIONAL FCT FEES $
48										SWITCH-IN ADDITIONAL FEES:	CHARGED TO CLIENT & SWITCH IN PROMO CHARGED FOR FCT FEES & DISCHARGE FEES	CHARGED TO CLIENT
49										BRANCH PAYS ADDITIONAL FEES:	CHARGED TO BRANCH & SWITCH IN PROMO CHARGED FOR FCT FEES & DISCHARGE FEES	CHARGED TO BRANCH
50
51										BRANCH PAYS:	*DEBITED BRANCH FOR FCT FEES OF $^
52										SWITCH-IN:	* SWITCH-IN PROMOTION CHARGED FOR FCT FEES OF $^ AND DISCHARGED FEES
53										DEBT ACCOUNT:	*DEBITED CLIENT ACCOUNT FOR FCT FEES OF $^
54			-3							SWITCH-IN BRANCH PAYS:	*SWITCH-IN CHARGED BRANCH FOR FCT FEES OF $^ & DISCHARGED FEES, CLIENT CHARGED ADDITIONAL FCT FEES
55
56
57	SWITCH-IN:	154.7						* SWITCH-IN PROMOTION CHARGED FOR FCT FEES OF $154.70 AND DISCHARGED FEES
58
59
60			18.3
Sheet2

 

[bleeet]

thanks this seemed to work better but when f57 doesn't have a number it says f57 empty and when i put a number in it the cell says unknown error