Hi all,
I am looking for advice on how to improve a disposal log that tracks all items that get broken in one day at work by customers. The first sheet is has all the usual company logos and headers at the top with 20 rows underneath to fill in details for each product. The details are product code, name, price, quantity, and brief desscription on what's wrong with it. This page is designed to be printed in landscape form. All this info needs to be typed in by hand.
The second sheet is formatted to fit labels. There's 2 labels per column and 7 rows with the cells and borders all aligned to fit these printable label pages, which means there's definately a lot of work involved if there is a slight change in label used. Also, since there are 20 rows for items on the first sheet, one page of labels has 6 remaining that are unused. Sometimes, multiple pages are needed, so you need to clear the rows and type the new products.
The label's cells are hard coded to cells on the first sheet to autofill, which is very difficult to expand. I would need to create a lot of pages with hard coded data to make an even number (LCM(14,20) = 140). Also, if I made hard coded pages, then on print you would need to select the pages you wanted.
Most of the data that we need to enter is in another Excel book -- the warehouse list. All items in the warehouse are logged in and broken items have their own location. The easy part would be to use Power Query to pull all the needed info into the disposal log and fill in the first page and then use repeat rows to make each page print the needed header stuff.
The advice I need is what to do about the second sheet? It is not uncommon to have 2 or more pages of broken items (I know it's a lot). At Christmas time, there can be a lot of pages as Christmas products are all breakable and customers and kids... we can have upwards of 10 or more pages. In order to satisfy Christmas, I would need to try to expand the labels page to the most amount we would ever need. Is there a better way of generating labels? It would need to be easy for the office staff to use. Hence the current method of just switching to a new sheet and printing. I already plan on using a button on the first page to refresh the query against a new copy of the warehouse list. Is there a macro to generate all the labels we need, then no selecting the number of pages they want? If it helps, the office computers have Excel 2016.
Many thanks.
