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