Stuck trying to dynamically search for information using parameters across multiple sheets

Hi there,

I've become stuck trying to create a dynamic master sheet in excel, which pulls similar data from several spreadsheets DEPENDING on what parameters a user is looking for.
There are 9 spreadsheets that contain regular data organized the same way on each sheet (all dates are in column A, all units of area are in column D, regardless of spreadsheet etc.)
What I want is for a user to be able to select any number of the sheets, any number of subtasks in those sheets, a start and end date, and/or a job # in any combination and be presented with all the rows of data that conform to those specifications in the master sheet, from which they could then create report-specific charts.

For example, you could simply lookup all the data available in the floors spreadsheet after May 20th of this year, and those rows would come forward without pulling anything from the other 8 spreadsheets, or any earlier records in time; or you could pull all records before August 15th of 2016 across all the spreadsheets, or you could pull all the data related to Job # 6455 from Floors, Ceilings, and Mechanical, without bothering to pull from the remaining 6 sheets, and so on.

Currently I'm stuck because I cannot create a compressed formula that checks ALL the spreadsheets or even a range of the spreadsheets, as soon as I try to check more than one at once with a logical operator it gives me #Value or #Ref depending which way I try to jig it.

I know I could do this if I checked EVERY spreadsheet individually and concatenated my functions so that when one spreadsheet was checked it moved on to the next, but this would prevent me from having a totally dynamic solution because if I later wanted to add a sheet to represent added functionality to production, I would have to manually add that sheet to ALL the formulas that sweep through the sheets, etc. etc.

Is there some key combination of functions or syntax that I am missing? Why can't I do something like INDIRECT("'"&A1:A9&"'A6:A10000") and store the names of my spreadsheets in cells A1:A9. In practice when watching the calculation step by step, this WILL show the sheet names and even combine them with the subsequent cell range, but then refuses to evaluate.

Help?
Ben

Okay, I've made up an altered spreadsheet showing just a random collection of data (ignore the sheet marked "finishes" which ironically is unfinished,
but would eventually constitute part of the data in the same format)

I duplicated the master sheet and made a before and after version.
The after version shows what SHOULD be found given the data available and the parameters entered which is all work on or after May 19, Job # 3277, in the sheet "Ceilings".
The key is I should be able to pick any sheet and/or subtask, or none, any dates, or none, and/or any job # or none, and the table would populate to contain copies of matching data for analysis.
E.g. if I picked plumbing right now, there is no data in the example sheet, but if I asked for all work done before this week, it would find all the data across all the spreadsheets that was entered before that day.
The instances count can be done either before or after the rows are filled in the table, it would just be an indicator whether enough data is available to be worth analyzing.

Unfortunately due to upload limits I had to delete several sheets but the process should be the same, just longer and more wearisome to execute when I import it back into the original file.
Please see attached,
Ben

That was an option I hadn't thought of, which might be easier? My hesitation on that idea is with the sheer amount of data I anticipate being added over time, which might make the table messy to work with if you didn't get very specific.

My original plan had been to only pull into that table relevant data (notably excluding the actually measured time and material unit value since i'm only interested in time/unit ultimately)
so the table would be empty, until such time as parameters were filled in at which point it would go looking for and present all the data that matched.

Honestly your idea seems far superior to mine in flexibility since the filters in the table would let you do almost everything I wanted anyway.
I would still be stuck at how to get dynamic data into that table to filter in the first place though, and I would then need a column for production sector the data is from.

This is amazing. I will take some time to give it a thorough lookover today and understand all the updates you've made; but I think if I can apply this to the total sheet it should solve all my needs.
Also, thanks for the reminder tip about tables, that will significantly help to keep the file smaller for sharing later.

This has been exceptionally helpful and useful as a learning experience. I'm really curious about these list-buttons you've managed to makeup, they seem exceptionally powerful as a tool.

Warm Regards,
Ben

One thing I'm just noticing is that it doesn't seem to like that the Plumbing spreadsheet is empty (it creates an empty row for it and also replaces Exteriors as the production sector on the last copied row) is this leftover from the line in the code for "Get new last row"?
I don't think this is really an issue since all the spreadsheets should get data eventually but just wanted to check and ask since VBA isn't my strong suit.

That's alright, as I said I don't anticipate any field of data being empty it is just currently so because I am still working out the system. Ultimately all the sheets will have relevant data and then the code should work perfectly. Thanks though!

Ben