Complex Lookup and Paste (Hard to describe in one sentence)

[mynian]

Greetings all,

I hope someone can help me with getting this set up.

Here is what I have (I have attached a sample spreadsheet for easy reference):

On the first sheet "Main", I have a drop down box data validated to a list on sheet "Stuff". The items in the drop down box correspond to the remaining sheet names. I have 3 empty tables that I need to populate with data from the other sheets (I'll explain how a little further down). I also added a push button for a macro run (if needed).

On the each of the other named sheets I have a table that contains data. Each table is defined and already named. The table names are the sheet name with a "t" at the end. The number of columns in these tables will never change, however the number of rows changes constantly, due to the nature of the data that I am pulling in.

This is what I need to happen when a selects a value in the drop down list (can happen with or without a button press) on the sheet named "Main":


For the table named "PFail"

1. It looks at the selection made in the drop down box to determine which sheet and table to get the data from.
2. It looks at the column "Prio Bag %" and looks for anything that is not "100%"
3. If it finds something that is not "100%" it looks at column "Prio Bag Count" to see if it contains "0/0". If it finds that, then it ignores that row.
4. If it is anything else, it takes the data from columns "Flight", "ETA", "Prio Bag Count" and "Prio Bag %" from the row(s) on the table that it is looking at and that meets the criteria, and puts them into the corresponding columns in table "PFail" on sheet "Main".


For the table named "Fail20":

1. It looks at the selection made in the drop down box to determine which sheet and table to get the data from.
2. It looks at the column "% Scanned in 20 mins" and looks for anything that is not "100%"
3. If it finds something that is not "100%", it takes the data from the from columns "Flight", "ETA", "Bags Scanned > 20 mins" and "% Scanned in 20 mins" from the row(s) on the table that it is looking at and that meets the criteria, and puts them into the corresponding columns in table "Fail20" on sheet "Main".


For the table named "SFail":

1. Same as the other tables
2. It looks at the column "Scanning %" and looks for anything that is <"90%"
3. If it finds something that is <"90%", it takes the data from the columns "Flight", "ETA", "Scanning Count" and "Scanning %" from the row(s) on the table it is looking at and that meets the criteria, and puts them into the corresponding columns in table "SFail" on sheet "Main".


For the 3 tables mentioned above, if possible, clear out any existing data that may be present, so that data that does not correspond to the selection made in the drop down box does not remain, if not all of the data gets over-written when new data is pulled in.

Thank you in advance for your help!

[MarvinP]

Hi mynian,

I have two possible directions to go with your problem.

1. Would it be helpful to press a button and have all your data sheets rolled into a single sheet? This way you could sort and filter using dropdowns however you wanted. You could also do a Pivot table on all the data from a single table. Would a appending of all you single letter sheets be what you need?

2. You could create a Dynamic Named Range for the data on each individual sheet that would grow or shrink depending on how many rows of data you had. Then you could use the Pivot Table Consolidation Wizard to combine the separate tables into a single table. I started to do this but didn't know if it would be helpful.

Does either of these make sense? Could you manually append your data to a single sheet and see if a pivot or table with auto filters will work?

[mynian]

Rolling all the data into one giant table would be useless. The data on a lettered sheet has no relationship to any other lettered sheet.

On the live workbook, each lettered sheet is a physical location code. There are multiple end users that will use this sheet. Each end user only needs to look at the results from one of the sheets, so combining all the data into one giant table would make it confusing to the end user, and the data in the tables has no unique identifier to indicate that it belongs to them. (An unfortunate result of the system the data comes from)