Calculating similar information/values as per two different dates

Dear experts,

Again I am in need of a solution for the following requirement. Hope you would give me your valuable support in this regard too.

Description about the situation

I have attached here an excel sheet which has a sheet tab called "Master sheet" and another called "Advanced Production Summary". The Advanced production summary already includes the planned production as per the master sheet( I didn't delete formulas as I though they would help in the new requirement).

The new situation is like this;
There are occasions that we do "advanced production" by pulling out quantities from "future dates" to date. This normally happens due to filling the current gaps in factory production lines.

However, due to this, I have to "reduce" the "advanced produced quantities" from "future dates" to see the "actual quantities" that are left for the future dates and "re allocate" quantities to fill the planned capacity in the future dates(weeks/months)

As a solution, I have put a remark column(AA) to mention the "situation" and the "production completed date"(AB). Now I want to show these quantities as per their "original dates" as per the date in the column P of the master sheet, and "completion date" as per the date in the col AB in the master sheet.

In other words, "the same quantity" will appear in two different months based on their "Original Cut off date"(col P), and "Advance Production Completion Date"(AB). It is also essential to show the "Advanced Completed Quantities" in RED FONT colour.

Can there be a formula solution for this? If showing this situation in the same sheet is difficult, it is okay to use two sheets and "generate a summary sheet"

Regards
Anuruddha

There is no sheet called "Advanced Production Summary" (but there is "Week and Department wise summe"?)

I also cannot see any sample output so can you please add examples (including "calculations") of what is required.

In A4

=IFERROR(INDEX('Master Sheet'!B$5:B$1000,SMALL(IF('Master Sheet'!$AA$5:$AA$1000="Completed",ROW($B$5:$B$1000)-ROW($B$5)+1,""),ROWS($B$5:$B5))),"")


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy acroos replacing INDEX range with appropriate Master range

Copy down

Hi John,
I was busy with some other stuff last few days.

The above solution worked for me very well.
However, some times "summarized data" cannot be seen on the "Monthly Advanced Production Summary". I noticed this happened when I added rows to the "Monthly Advanced Production Summary" sheet.

Anyway, today I did some changes in the other sheets in this excel work sheet(it has some more sheet tabs, and I deleted them to reduce the size), the data on the "Monthly Advanced Production Summary" disappeared.


Attached the error sheet for your reference.

Regards
Anuruddha

What is wrong with the attached?

formulae ..

..confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer

Ohh..I am sooo forgetful. Curse my stupidity.
Thanks John..

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.