[johnm524]

I've attached an example of the spreadsheets. They will be formatted the same way (same headings) and will be stored in the same directory. The headings are show in blue, I'm creating a drop down menu of the items in green, and the orange represent misc info. What I need is to develop is a master document (seperate spreadsheet) that will allow people to select from a drop-down menu (choosing by heading) and retreive all info that matches their selection, kind of like an auto-filter except it will pull data from multiple spreadsheets.

Example: In the master file there will be a drop down for type (C1) and it will list all the available types in this category (Plastic, Paint, Glass, etc...). If the user selects Glass the the master will search each spreadsheet and display each row of data that has Glass in column C.

Now it gets more tricky because each spreadsheet represents a project I'm tracking so they will be constantly updated with additional information (rows of data). Thus coding can't be range based unless it references something large scale like C2:C2000, I would prefer if it'd scan the entire column C instead. Also more spreadsheets are likely to be developed in the future. This is why I have them all stored in the same location and would prefer if the master would scan everything in that directory to generate its queries.

I'm horrible at explaining stuff so please ask if anything need clarifying.

[Zack Barresse]

Hi there,

Why would you not use a database here (i.e. Access)? It seems a perfect fit. Plus you can have your multiple users, but they would be accessing the same data [tables], thus keeping data integrity and stopping redundancy. Having the data in one location would save a lot of time and resources I think. Are you open to a database? If not, how about having the users access a single Excel file?

[johnm524]

Yeah I had originally setup something in access but my Boss didn't think it was user friendly since most of everyone here doesn't know how to use access. Also the problem with having everyone use a single spreadsheet is version control and maintaining integrity. If I have it setup so that the master file cannot be edited than that will save me a bunch of heartache.

[Zack Barresse]

Not user friendly??? But you can make the UI any way you want to! I don't get that.

Well, anyway, we can do this in Excel for you. Some questions though. You say that a user will select a category and you want the data pulled from all workbooks. How often will this happen? Do you want a button for the user to click to grab the data? It could be expensive in terms of resources, opening workbooks, copying data, closing workbooks. If this is run often it might tax resources. Is that ok with you?

What does the master workbook look like? Or are we free to build what we like here? Is there just one master workbook? The spreadsheet you posted are the user workbooks, or an example of one. Where will these spreadsheets be located? How will you know where to look for these? Will it always be named the same? What worksheets will house the data? Will they always be named the same? If not named the same, will they be the same position? Are there ever going to be hidden worksheets in the spreadsheet files? What about past imported data? Shall it delete all imported data everytime?

I'm wondering if this shouldn't be in an add-in. What version of Excel will this be run on? The lowest version? The highest version? Where will the master list (of heading information) reside? If this is a list of information that you will control it would make the data easier to track. Is this on a network? Some more information about that would help as well.