Populating sheets from entered data

Hello Excel dudes, and dudettes.

I'm trying to create a workbook that will create labels depending on information entered into a data sheet.

My problem is trying to get excel to see what is entered where in a line, and then populate the labels with the relevant information.

I've attached an example sheet, with information on it about what goes where... if anyone can help me solve this riddle, they get a virtual hug from me, thanks.

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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

formula in A12 to:

Please Login or Register  to view this content.

Formula in A15 will be:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Ok just read through this, and had to have a cigarette after, I'm impressed

A couple of things then:

The information in cells A6, A9, and A12 would be simply drawn from the DATA sheet and populate each subsequent sheet, I can do that bit (I know, I'm terrible). Unless there is a higher purpose to your code there that I dont understand...

Instead of using H1 for a box reference, is it at all possible to use the sheet names? As they are 1 to... (how many sheets can a workbook have by the way?)

The Carton section needs to read 1/13, 2/13, 3/13 etc, so I was wondering how to get the two parts together, firstly the box number then the total number of boxes where information has been entered.

Thank you very much for your reply so far, its opened up a whole new world for me.

Regards

*edit* I forgot something too. The size type that will appear in A15 will depend on what size type (A, B, C or D) is chosen from the DATA sheet (entered in cell D5). Or is that just wishful thinking?

You need a special formula to get the sheetname:

Please Login or Register  to view this content.

You can put this in H1, or you can substitute H1 in the formulas with that.

In the attached I added it to H1, but I also subbed it in the formulas.. so if you want you can delete the H1 cell altogether.

Also, I thought you would only want the labels for boxes that have quantities entered in them, if not, then simply revert back to your original formulas for those A6, A9 and A12 cells.

The formula in A15 already accounts for the selection of "size type". Try it

To get Carton, I added a helper column in the Data sheet to check if the rows have quantities in them. Formula in J12 of Data sheet is:

Please Login or Register  to view this content.

copied down

Then in A20 of each sheet:

Please Login or Register  to view this content.

or if you choose to keep H1:

Please Login or Register  to view this content.

Not a dumb question... I am the dumb one

I only replaced 1 of the hard references to the sheet number.. and there are 2.

Replace formula in A18 with:

Please Login or Register  to view this content.

Remember to hold the CTRL+SHIFT keys and press ENTER to confirm it.