Thread: Dropdown list to pull data from different spreadsheets

Hello everyone. This is my first post here - I'm not sure whether I'm in the correct section and I apologize if I'm not.

What I need to do right now is create a dropdown list with 45 names. Then, I need excel to go to a spreadsheet based on the name picked from the dropdown list, find a large amount of data from different sections of that spreadsheet and pull it into the current spreadsheet, then I need to make a few charts out of that data.

I'm not quite sure how to go about doing that. I would prefer to do this without VBA if possible...

hi smit,

if possible upload a sample file, which will reduce the time to get to a solution

I would love to but how would I do this? I can't upload 45 files (even if I could the information is proprietary) and the file with the dropdown is a new spreadsheet with nothing in it.

I don't think this would be possible without VBA - the only other alternative formula I can think that can change based on a selection would be INDIRECT, but this doesn't work unless the workbook is open - you'd therefore have to have 45 workbooks open for it to function correctly.

Either way, you're asking for a lot, and no-one could really give you a clear answer without at least some dummy data to work with. Could you mock up one of the 45 files and show which data you are pulling, what needs to be graphed, etc?

I'll check with legal to see if I can upload anything.

What I want to do is go through the 45 spreadsheets and name the sections I want in the chart. Since the spreadsheets aren't 100% uniform, I want to tell it to graph the quarter/year with the named section "REVENUE" and name the row with depreciation into "DEPRECIATION" that way I won't have to give cell ranges but a name. So hopefully that will fix alot of the problems.

If they're not uniform, then the chances of being able to do this without VBA just became very, very slim. But again, without seeing a rough idea of what your data looks like, it's really hard to offer any kind of solution.

Even if you want a VBA-based solution, people need to know where to tell the script to look, hence the need for some dummy data. Obviously remove anything proprietary, change all real names/numbers, etc. Then I don't see what there would be to worry about.

It would still be difficult if I named the ranges in the separate spreadsheets? Why would the uniformity matter in that case? Basically, I just want a way to pull data from different spreadsheets based on what is chosen in the dropdown. I really need vba for that?

I uploaded a file with fake numbers and a part of one tab in a spreadsheet. I don't see how it will help though.