Order scheduler formula help required please!

Hi all,
I am trying to write an order scheduler and need some help with the formulas please.
On the attached file, the orders I get, come in weekly, in the format on the WK1 & WK2 tabs.
There can be multiple orders each week for each product, and they can change weekly. (Quantity change, required date moved forward or backward, or a new order).
On the tab test, I can get the formula to work on single orders (highlighted cell).
On the summary tab (where I want everything pulled through to, I have tried variations of that which I can't get to work, and also a drop down list on the summary tab in cell B3, that when you select the week, it pulls through the data onto the summary tab from the respective week. The formula in K1 is one I was trying for that.
Any help would be appreciated.

Many thanks in advance.

Hi John,
many thanks for that. It helps and fixes the part for pulling data through. Unfortunately it will only pull through the first instance of each order and not all of them. It does also not resolve the part on the test tab, and how to make that work on multiple orders on the summary tab.
I have attached a revised file with the amended formula to show that working for that part.
Many thanks for your help thus far.

Kind Regards,
J.

Hi both,
many thanks.
I have attached a sample file with data manually added in.
In simple terms, when the week is selected from the drop down list, I want the formula to compare with the previous tab (they will be labelled sequentially by week) and look at the orders.
If the order is different, it will display a message "New Order", if amended quantity, display "Amended Order", if the date has moved back, "Delayed", or moved forward to "Brought forward".
This works as a prompt so when an order file is received it can be dumped into this, and will highlight very quickly anything that has changed from the previous week.
To answer some of your questions John:-
No the data in the test tab is just dummy data there to show the formula I had working against single order amends, but not multiples.
Partial shipments would be considered on the following week, as it would then show as an "Amended order" as the amount is different :-)
The stock control element, I have something else I have worked on that does an element of that. Happy to share sometime. This is more to show what orders have come in, and if any have changed.
The finance part is not relevant to any of this, as that is handled via an ERP. This is just to assist initially with visibility of orders with a view towards (afterwards) planning materials etc.
Hopefully that will make sense!

Many thanks in advance.

Thanks for this.
How would this work with a week 3, week 4 scenario please? The formulas all look to reference WK2. In Summary, cell B3 has data validation for WK1 - 53.
Thanks again for your help.

Hi, thanks for the reply.
In answer to the questions (hopefully!)
Yes, an order could be changed in Week 4. (This actually happens quite a lot!)
I had considered having a column for weeks and that is what is on the tab labelled test (this is what I started with). But I couldn't get the layout to work very well with multiple orders etc, but happy to look at it if you can think of another way.

Thanks,

Thanks for that.
A question on this. If an order is placed on WK1, and has not been amended, or changed and is on WK2 (timed to deliver in the distance, and on subsequent weeks, when I copy that across, it shows up as a new or amended order when actually it is still the same existing order - just not completed yet.
How does it display just notrmal orders that are not changed? Otherwise every week, every order will show as new, when they are not?
Hopefully that makes some sense?

Many thanks.

Ahh.
I see.
I understand the adding weeks into the column now. Unfortunately, I am not sure that will work. That is because the reason for WK1 and WK2 is to compare the orders. That is essentially the delivered check I guess. If the order is not on the WK2 list, it will not go onto the summary.
If it is on the WK_Order tab, I "think" all that will happen is there will be a long list on the summary tab, and it will become unwieldy to read and figure out live orders as old ones will still show, if I am understanding it correctly?
The only other thought I guess to utilise the WK_order tab would be if there is some way to amend the formula to look at the previous week in column A, and if the order is not repeated anywhere in the following week it is removed. Kind of the reverse of the new order part if that makes sense?

Many thanks.