Amend Forumla

[Pete]

Hi having trouble in trying to make sure that the following forumal is right

.Cells(startrow + 1, "F").Resize(1, 48).FormulaArray = _
"=if(or(" & _
"iserror((Revenue_" & dem & "_" & sup & _
" - " & "Purchase_" & sup & "_" & dem & "))" & _
", VLOOKUP(B" & startrow + 1 & ", SupplyData, 3, FALSE) = FALSE" & _
")" & _
",0" & _
"," & _
"(Revenue_" & dem & "_" & sup & _
" - " & "Purchase_" & sup & "_" & dem & "))"

could someone cast there eye over it an tell if i have done something wrong?

[Norie]

Pete

Are you sure this should be an array formula?

I can enter it as a normal formula but not with FormulaArray.

[Pete]

Yes.......the original formula which works is

"=if(iserror((Revenue_" & dem & "_" & sup & " - " & "Purchase_" & sup & "_" & dem & ")),0," & _
                    "(Revenue_" & dem & "_" & sup & _" - " & "Purchase_" & sup & "_" & dem & "))"

But i am trying to make the values zero or "-" if False is selected in the main worksheet

[Bob Phillips]

What doesn't work about it (although I too don't think it is an array formula)?

[Pete]

If i have made a mistake from the original verison of the Array formula that works........

"=if(iserror((Revenue_" & dem & "_" & sup & _" - " & "Purchase_" & sup & "_" & dem & ")),0," & _
"(Revenue_" & dem & "_" & sup & _" - " & "Purchase_" & sup & "_" & dem & "))"

How can i change the above so that if False is selected in the main worksheet in column D.....then positive cashflows become "-"

[Norie]

Pete

I think we need to know more about the actual formula and it's purpose, then we can deal with the code.

[Pete]

The purpose of the final formula should be that if the user selects FALSE in Column D the main worksheet.....Agianst a selected customer then the cashflows for that customer in the cashfloe worksheet become zero until the User select TRUE again....

The original ARRAY works fine which works out the actual cash flows from the individual worksheet

"=if(iserror((Revenue_" & dem & "_" & sup & _" - " & "Purchase_" & sup & "_" & dem & ")),0," & _
"(Revenue_" & dem & "_" & sup & _" - " & "Purchase_" & sup & "_" & dem & "))"

BUT THE PROBLEM ARISES:-

When i try and amend the above to change the values to zero if FALSE is select and IF TRUE is selected then post the results from the actual cashflows.....which works fine with the posted Array formula.

I hope this summary helps.