Variable Lookup of data across different tabs while skipping blanks

[Nurseryboy]

Hello,

Please help!

I run a distribution company in which we pack different products into different boxes and send all the boxes together in one shipment to one location. We have different work stations that work on the same shipment at the same time and pack all the products (in that shipment) independently of each other. We have to know exactly what’s in each box upon packing and, thus, we need to make sure that all the stations follow the same process.

To help the coordination of this, we have named each station with a letter, starting with “A” and progressing sequentially up the alphabet. So, Station A is packing their own set of boxes, labeling them A1 (as the first box packed in their station), A2 (as the second box packed in their station), A3 (as the third box packed in their station), etc., while Station C is only packing their boxes, labeling them as C1 (as the first box packed in their station), C2 (as the second box packed in their station), and so on.

When all the boxes in all the stations have finally been packed, we need to label the boxes sequentially and let our customer know exactly what the number is of each box and exactly what in each of the boxes (“Final Box #” 1, 2, 3, … 12, as in the attached spreadsheet).

This CANNOT be in VBA or use any Conditional Formatting, because we create this process in Excel upload it to Google Sheets for the various stations to process, then (after the process is completed) we download it back into Excel. Thus, any VBA coding or Conditional Formatting would be stripped out of the file during Excel/Google Sheets conversion.

I need help on figuring out how to do this in Excel without VBA (i.e. using formulas, lookups, etc.) or any Conditional Formatting.

Can anybody help, please?

A couple more caveats:
• We have more work stations than just 5, but if this can be figured out for 5, then it can be figured out for more stations
• Any number of stations (from just a single one to all of them) can work on the same current process

I have attached a sample Excel spreadsheet of what I want to end up with (I did it manually), but I need to do this “dynamically” as a formula or lookup or something, but again no VBA or Conditional Formatting. A sample output of what I’m looking for is highlighted in YELLOW.

Any help or ideas would be truly appreciated. Thank you in advance,

Yury

[dflak]

I have some quick questions:

Do you need the expiration dates in every other column? It's connected to the SKU in column C and is the same all the way across the row.

Considering the way you have the data organized, and the restriction on not using VBA, then the solution will require a lot of "hard coding" and you may have a lot of blank data. Can we assume that there will be no more than 10 boxes per work station.

I suppose the reason you are doing this with Google Sheets is that the workstations don't have real excel on them. Is the a reason the workstations need to see the summary? If they don't need to see it, then why not download it to real excel on the manager's workstation?

[Nurseryboy]

Hi dflak,

Thank you for your response. Here are the answers to your questions:

1) If possible, we would like to have the Expiration Dates in every other column. However, if you can figure out how to do the Final Box numbers through formulas, etc., then we would just use that tab as a basis for an "input/output" spreadsheet where we would use this "input" tab along with an "output" tab to display the Expiration Dates on every other cell (in other words, in the "output" tab, we would lookup each of the sequential boxes that you accounted for and lookup the Expiration Date on every other cell).

2) We have no problem "hard coding" cells in an "eventual output" tab (in an "output" template), if need be...we would just rather not "hard code" each shipment manually.

2a) In regards to the number of boxes, there is no way of anyone knowing exactly how many boxes we will eventually have per station prior to them working on it. In other words, we may have 2 boxes in a particular station, 30 boxes in another station and 15 boxes in a 3rd station (again, no way of knowing number beforehand...each of our items are different sizes and may need to be packed in different sized shipping boxes, that's why we have no way of knowing ahead of time).

3) Yes, you are correct. The reason that we upload this document to Google Sheets is that each work/shipping station has a Chromebook (not a laptop) in order to seamlessly share all data in real time. In regards to the Summary tab, although the individual stations don't need to see it most of the time that they are working on the shipment, they do need it to coordinate with other stations and, at times, need to glance at it during packing. There eventually will be a "Manager's Dashboard" within Google Sheets so that managers can see the overall and individual progress for that shipment (and all shipments combined) in real time. Eventually, we may download all of the shipments into Excel to do more "heavy duty" analysis (after the fact), but a dashboard in Google Sheets in the "in process"/real-time information that gets its data from these shipments.

BTW, this spreadsheet is a simplified/"watered down" version of the overall spreadsheet (i.e. the Summary tab has other data/uses as part of our overall process).

dflak, I know that this may be a hard task to do without VBA, but any and all input is very much welcome. If this cannot be done without VBA, we would be OK with...

a) creating the original document in Excel
b) uploading this document to Google Sheets
c) have everybody work on Google Sheets document
d) downloading Google Sheets document into Excel
e) having a template with VBA in it to create an "output tab" from the downloaded info (that we would "copy and paste from the downloaded file into a template that has VBA in it) that would include all the necessary information that we're looking for

Please let me know your thoughts and thank you again,

Yury

[dflak]

I'm beginning to think this is "Mission Impossible" but before I toss in my towel, I'd like to try a couple more things.

I recently had to review google docs and found them marginally useful. I am aware of their many limitations.

[dflak]

I've given up on Google Sheets - it's way too weak to handle the issue. It's basically not much better than a PDF file that can do some simple formulas.

Take a look at Microsoft's Office Online - There is a chomebook app for it and I think it's a bit more powerful, but I still think it falls short of the task.