2 sheets for daily production and production schedule for a PO (month) =Status

rruchty

I have two sheets. One sheet for tracking daily production and another sheet that tracks the production for the month. I have a status column that matches on both sheets. On the production schedule I have that status of the reference number. If production is working on that reference number it has a status of Work in Progress. In Daily production they could be working on the same reference number but on 2 different lines for the day. on one line it could be could be work in progress and on a different line it could be Assembly complete. I want to be able to write a formula that will look for the reference number and search the status column until both reference numbers status show assembly complete. I want to be able to control the status on the daily production and when all of the reference numbers are assembly complete update my production schedule to show assembly complete for that reference. I hope this make sense. I have tried vlookups but it will only evaluate the very first value found and not go past that to make sure they are all in an assembly complete status.

Daily Production Sheet

Production Schedule

Elska19

How many statuses do you have in the Status column and is the Assembly Complete status your priority? I'm wondering if you could perhaps use a helper column in the Production Schedule sheet if you're looking explicitly for the Assembly Complete. For the Status in the Production Schedule sheet, is this based on the VLookUp Value?

My suggestion if you're looking for when all rows in the Daily Production Sheet of the same Reference # are complete would be to set up a column like this in the Production Schedule Sheet:

Helper Column:

=COUNTIFS({Reference # Range from Daily Production}, Production Schedule Value of that row, {Status Column from Daily Production},"Assembly Completed")/COUNTIF({Reference # Range from Daily Production}, Production Schedule Value of that row)

This should give you a decimal value (or whole number so (y/x or x/x) based on how many are at Assembly Completed out of the total. You could then have the Status Column in your Production schedule sheet be set up to a formula like this based on the helper column that would be pulling from the Daily Production Sheet.

=IF(Helper Column Cell=1, "Complete","In Progress")

or something of that nature where once it's at 100% (x/x) of all references being at Assembly Completed the Schedule sheet could then update it's status from that. This would work if you have many of the same reference numbers in the daily production sheet but only one instance in the schedule sheet.

Hopefully this helps!

Elska19

How many statuses do you have in the Status column and is the Assembly Complete status your priority? I'm wondering if you could perhaps use a helper column in the Production Schedule sheet if you're looking explicitly for the Assembly Complete. For the Status in the Production Schedule sheet, is this based on the VLookUp Value?

My suggestion if you're looking for when all rows in the Daily Production Sheet of the same Reference # are complete would be to set up a column like this in the Production Schedule Sheet:

Helper Column:

=COUNTIFS({Reference # Range from Daily Production}, Production Schedule Value of that row, {Status Column from Daily Production},"Assembly Completed")/COUNTIF({Reference # Range from Daily Production}, Production Schedule Value of that row)

This should give you a decimal value (or whole number so (y/x or x/x) based on how many are at Assembly Completed out of the total. You could then have the Status Column in your Production schedule sheet be set up to a formula like this based on the helper column that would be pulling from the Daily Production Sheet.

=IF(Helper Column Cell=1, "Complete","In Progress")

or something of that nature where once it's at 100% (x/x) of all references being at Assembly Completed the Schedule sheet could then update it's status from that. This would work if you have many of the same reference numbers in the daily production sheet but only one instance in the schedule sheet.

Hopefully this helps!

rruchty

Thank you! I was actually able to do a helper column after I did a sumifs for the total amount of cases for my quantity >= 100% on my production schedule. If we produce more than the PO amount it will place a check mark in my helper column. I then set up automation to change the status to Assembly Complete.