Transfering data from one sheet to another along with sorting and removing blank fields

**madhatr** · 01-23-2013, 02:46 PM

Sorry for my ignorance, but I am new to using any of the more advanced features of Excel. I have basic programming knowledge in the area of C and AHK, but for a current project I need to do everything within Excel.

So here is what I am trying to do. I have a workbook in where I will be creating several work order sheets on a daily basis. On these sheets I have set information about a product, serial numbers, order numbers, etc. This information will always be filled out. I also have details about the what is in the product. Some of these items will always be filled out while others will be left blank. Each row designated for the option items are organized by location in the product so if the option is not present that row will be blank. The optional items are listed in 3 columns, a part number, quantity and part description. I also have another sheet with a label that will be printed out to a Zebra printer. This label will contain the set information about the product, the set optional items and all of the other optional items.

What I would like to be able to do is transfer the information from the work order sheet to the label sheet. My original idea was to define each field on the work order sheet and have the label point to that defined field. I would then add a button on the work order sheet that would change all the defined fields by changing the page name to that of the current work order page and then trigger excel to choose the Zebra printer and print the label page.

First of all is this the best way to do this or is there a much simpler method to achieve the same results? Also what would be the best way to have excel pull information from the first optional item field, if that field is blank then move to optional item field 2 and so on until it reaches a field with data in it. I was going to try a long function like "=IF(ISBLANK(Option1PN),Option2PN, Option1PN)" but continuing it continue checking fields until it found one with data. The problem with doing this is the next line in the label would need to know to not start at Option1PN but instead to start after the option that was used in the prior row. I also had the idea of having it copy all the optional fields to another area of the work order that would be hidden and then sorting just those columns to get rid of the blank fields and then just pulling the data from there.

If anyone knows of a better way to do this it would be much appreciated.

**arlu1201** · 01-25-2013, 07:13 AM

Is this solved?

If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit.

**madhatr** · 01-25-2013, 10:06 AM

I solved it by not over-thinking it. I was making it much more complex than it needed to be. Instead of having the label on it's own page I placed it on the same page far away from the rest of the table. To remove the blank items and organize them I made another area off screen with 7 columns. The last column each row just had the number of the row in it, as in Y1's data was "1", Y2's data was "2" and so on. In the column beside it each row pointed to the item in the table that listed the quantity of the item like X1 was "=IF(G17>0,1,0)" X2 was "=IF(G42>0,1,0)" and so on. This was done because the table was not listed in the same order of priority as I wanted the label to be, the if function was there so later when I sorted everything items with higher quantity would not be moved to the top of the list.

I then wrote a macro that copied the data from all the options over, the items quantity data was copied one at a time to to column T in the order of priority that I wanted it printed, the options part number was copied in the same fashion to column S and the option's description were copied in order to column U. These were copied each column at a time because the tables information was ordered as quantity, name, number but the label has it listed as number, quantity, name. I then copied the data from column X and Y to V and W. Finally I had it sort S1:W15 in the descending order of column V. What this did was it kept the order that I wanted listed and moved the items that had a quantity lower than 1 to the bottom of the list. Then all I had to do is have the label that I had recreated in the AD55 to AK92 area of the page point to the ordered information in the list. The order was correct and all the blanks were at the bottom which left them all showing "0" for the data. Changed the format of all those fields to the custom format of 0;0;"";@ and that left them truly blank. Then I simply had the macro choose the print area, format the page margins, choose the printer and print it.

I am sure it is still much more complex and sloppy, but it works.

Transfering data from one sheet to another along with sorting and removing blank fields

LinkBack

Thread Tools

Rate This Thread

Display

Transfering data from one sheet to another along with sorting and removing blank fields

Re: Transfering data from one sheet to another along with sorting and removing blank field

Re: Transfering data from one sheet to another along with sorting and removing blank field

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions