See if the attached helps you. You will need to allow Macros.
First, I added a user-defined VBA function (Alt+F11, Insert|Module). Note: This is not my code, but it is used by many in the Excel world.
This will be used to concatenate the results in the Size and Quantity boxes.
Then in Each sheet, you would add a cell identifying the Box number (ie the sheetname) so that we can reference them in a consistent manner without have to manually change each formula. I chose H1. Use the same cell on each sheet.
Then you can select Sheet1, hold the Shift key down and select the last sheet. This will allow us to put the formulas in once and all the sheets will be populated.
In A6, change the formula to:
This checks if any numbers are entered in the respective Box row in Data sheet. If there isn't, it will remain blank, else it will fill in the details. Notice that it references H1 (box number cell)
change formula in A9 to:
formula in A12 to:
Formula in A15 will be:
This is the formula that uses the UDF function. This formula needs to be confirmed with CTRL+SHIFT+ENTER and not just ENTER to work.
similar formula in A18:
again, B]this formula needs to be confirmed with CTRL+SHIFT+ENTER and not just ENTER to work.[/B]
I am not sure about the B20 (Carton). What exactly is supposed to go there? I.e. what is expected result and why?
To ungroup the sheets, right click on any box tab and select Ungroup sheets
Bookmarks