PDA

View Full Version : Amend Forumla



Pete
06-09-2008, 10:11 AM
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
06-09-2008, 10:26 AM
Pete

Are you sure this should be an array formula?

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

Pete
06-09-2008, 10:32 AM
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
06-09-2008, 10:38 AM
What doesn't work about it (although I too don't think it is an array formula)?

Pete
06-09-2008, 10:42 AM
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
06-09-2008, 10:51 AM
Pete

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

Pete
06-09-2008, 11:08 AM
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.