Formula for Forecasting Orders

[caf20012]

Hi All,

I'm currently having issues at work producting a live forecast of orders placed.

Ive attached 2 Excel documents showing what i currently update and what i need updates.

On the "purchase order log" i'm basically logging orders placed. Depending on the product orders allows me to input the "Main & Sub Catogory" of a product. This allows me to keep in budget on the "summary" tab

On the Account Forecast sheet i'm currently manually typing orders which fall into the order date from the "Purchase order log" This is an example which ive attached but i manage around 50 orders per day from various suppliers. You can imagine its hard to keep up and very time consuming to log everything twice.

What i need help with is a forumla that filters through the "Lookup", "Supplier", "Order date" & "Manual Order Value"columns on the PO log and uses this information to put the correct value into the correct cell on the Account Forecast Sheet on the correct Tab.

I was thinking of using =SUMIFS but finding it hard to filter through 4 different columns to enable the values to go into the correct cells on the Forecast sheet. Ive highlighted on the Account forecast sheet cell "J5" which shows the value on the PO Log if it was a cleaning order.

please find documents attached

Any help would be greatly apprichated. This is driving me crazy and time consuming.

[Jul Stev]

Hi caf20012,

Maybe you can use sumproduct for this case. try to put below formula in the cell that you highlighted and copy as needed, tell me if anything wrong..

Please Login or Register  to view this content.

And btw what do you mean by

using =SUMIFS but finding it hard to filter through 4 different columns to enable the values to go into the correct cells on the Forecast sheet

i found this method easier than sumproduct, all you need to do is play with the absolute reference

[zbor]

all you need to do is play with the absolute reference

You should rather know when and how to use them than to play with

[Jul Stev]

well, thats true zbor.. but i actually learn how to use it by playing with it :D

[zbor]

Find some cool video and then after that play a lot more
like:
http://www.youtube.com/watch?v=NmVMjQzseLA

[caf20012]

Hi Jul Stev,

Excellent work! It seems to be working correctly and performing the function i need. My next question is that formula is pulling information live from my manual data entering. On the Account forecast sheet there will be days ill need to predict the value of orders. The Formula you provide practically overwrites my manual entries and sets them to 0. Is there a way of putting a manual figure in a cell which includes the formula and if an order is placed for that date (Added to the Purchase order log) it updates the value with the actual instead of the predicted one entered?

This is only because the majority of the month i need to predict the spend and if i've entered live orders it needs to override the predicted so i can continuously give a live forecast of month end spend at any date.

Appreciate the information you've given so far! Work exceptionally well!

[caf20012]

Anyone who can help with my question above?

[caf20012]

I'm using this formula =SUMPRODUCT(('[Purchase Order Log.xlsx]Orders'!$C$3:$C$17=$A5)*('[Purchase Order Log.xlsx]Orders'!$H$3:$H$17=$C5)*('[Purchase Order Log.xlsx]Orders'!$L$3:$L$17=J$4)*('[Purchase Order Log.xlsx]Orders'!$P$3:$P$17))

If the values between $C$3:$C$17 is too large its giving me a #value! error in the cell.

Could i use array 2 to continue the remaining cells if the difference between the selected cells is too high?

(example
=SUMPRODUCT(('[Purchase Order Log.xlsx]Orders'!$C$3:$C$17=$A5)*('[Purchase Order Log.xlsx]Orders'!$H$3:$H$17=$C5)*('[Purchase Order Log.xlsx]Orders'!$L$3:$L$17=J$4)*('[Purchase Order Log.xlsx]Orders'!$P$3:$P$17)),(('[Purchase Order Log.xlsx]Orders'!$C$17:$C$20=$A5)*('[Purchase Order Log.xlsx]Orders'!$H$17:$H$20=$C5)*('[Purchase Order Log.xlsx]Orders'!$L$17:$L$20=J$4)*('[Purchase Order Log.xlsx]Orders'!$P$17:$P$20))

Ive tried using $C:$C but because ive got filters it won't allow me too use.

The current Formula i'm trying now is as follows:
=SUMPRODUCT(('[POLOG]SGH'!$C$2018:$C$2500=$A5)*('[POLOG]SGH'!$I$2018:$I$2500=$C5)*('[POLOG]SGH'!$M$2018:$M$2500=D$4)*('[POLOG]SGH'!$Q$2018:$Q$2500),(('[POLOG]SGH'!$C$2501:$C$2502=$A5)*('[POLOG]SGH'!$I$2501:$I$2502=$C5)*('[POLOG]SGH'!$M$2501:$M$2502=D$4)*('[POLOG]SGH'!$Q$2501:$Q$2502)))

The formula above is giving me an #VALUE! error, Really frustrating as if i extend cells 2018:2500 to example 3000 it gives an error as well.

[caf20012]

Solved,

PO Log has a comma after the value which the formula didn't like.

Thanks for the help!