I have two worksheets in a workbook. The first worksheet is just a classic table of data for every single pallet location in a warehouse (upwards of 20,000 rows). For each location, there are eight fields of data:
1. Cell# on worksheet 2 that corresponds to this location
8. Travel Seq
The second worksheet is a visual layout of the warehouse with each cell being a unique location relating to one of the rows in the first worksheet.
In Cell A1 of the second worksheet, I have a drop-down list where the end user can select any one of the seven fields (not including Cell#). When they make a choice, I want the second (visual) worksheet to update all 20,000 cells to display the piece of data associated with the users selection.
For example, if the user selected "Location", I want all 20,000 cells to display the unique location# associated with that cell#. Then, if they select "Capacity", I want all 20,000 cells to update and show the pallet capacity for the location associated with that cell#.
I know how to make the worksheet update when the end user makes a selection. But, I don't know how to write the formula for each cell on spreadsheet 2.
Would I use some kind of VLOOKUP formula? If so, what syntax do I use to have it look up the curernt cell# as the lookup value? And, how do I tell it how many columns to go to the right to get which piece of data depending on what the end user wants to see?