I am trying to build a three-point dashboard fed by 3 spreadsheets (2 external).
I would like to RAW MATERIAL STOCK file to supply data to feed columns I and J of the "Copy of Production Plan Aid- 09.03.23" -"copy of Bom Pull out" Sheet. I have tried doing this with a Vlookup but it does not like me. If the item is in-stock "column I" should show the stock amount. for the components (green highlight) If in stock, the about should also show in Column I.
I would like the Blue cells of the "Copy of Production Plan Aid- 09.03.23" -"copy of Bom Pull out" Sheet to be filled by totalling the instances of each item. The green highlighted components should auto-calculate. I initially thought it should be a Vlookup + sumif + importrange but that is not working for me.
For the dashboards, I have added in the headers I would like.
For what we can make, I want the sheet to show what item codes are in stock and ready to go in Dark green - this will be fed from "Copy of Production Plan Aid- 09.03.23" -"copy of Bom Pull out" Sheet (column G of the blue highlights). for items where we have enough components ( green highlights ("Copy of Production Plan Aid- 09.03.23" -"copy of Bom Pull out", columnA) I would like them to show in Pale green. If we do not have enough stock, I would like it to show in the Amber and Red part of the dashboard.
Amber & Red I would like any missing componentry to show in this dashboard. I would like it Colour coded to show products missing 1-2 components in orange and items missing more in red.
Bulk Formula In "Copy of Production Plan Aid- 09.03.23" -"copy of Bom Pull out" Sheet i have added the formulas about the ingredients that make them up, Is it possible to scan the sheet and have it continuously calculate any formulas below safety stock level ( shown "Raw Material Sheet" column AQ) and add them to the dashboard in pale green? Again any formulas that cannot be made would be shown in the Amber & Red dashboard - the Bulk one would only show orange or red depending on how many component ingredients are missing.
I hope i have explained it well, Please let me know if more is needed.
