Dropdown menu that decide what data to use

Hi guys,

I would like to make a dropdown menu that paste data for a given target group into sheets according to years (1999-2014).
The datasheets I have are based on a target group and a year ranging from 1999-2014, to it, I have set up vlookups for each year for my analyses.
This means that I normally would have to paste data into the sheets ranging from 1999-2014 according to the target group I want to use.

1. Where do I put the data I have as it should work as a database, I have 15 target groups and data for each year ranging from 1999-2014?
2. How do I set up a dropdown menu to select target groups?
3. When target group is selected, I want excel to automatically paste data into each sheet according to year.
4. Will all the sheets make the workbook unstable? At the moment each target group has its own workbook with sheets for all the years.

Drop-down example.xlsx

Thank you in advance.

Hi Jakob,

If it's possible, I'd suggest that you place all your data in 1 sheet. Then, if Iunderstand correctly, you'll only be needing 1+5+1=7 sheets: 1 for the database, 1 for each year and then 1 for the dropdown-menu.

As to how you should create your dropdown-menu, you should create a list of the names, that correspond the targetgroups in the database. Then you use "Datavalidation" for setting up the dropdown-menu.

This done, you can use some form of lookup function to populate the sheets for the individual years with data from the database.

Does this help you?

\\ Søren

Hi Søren,

Thank you for your quick reply and input.

I afraid that my overview of data will be lacking if I set it up all together, since each target group (and each year) is 801R*212C.
Furthermore, each sheet named 1999,2000,2001...2014 has references (v-lookups) to the mentioned analyses.

Can you think of any other solution than a dropdown, which can trigger excel to load data into each sheet according to target group? I was thinking about an "if" formula, but that will be too hard to process unless it can be applied to anarea and not each separate cells. I'll look into that.

Let me know if you get any ideas.

So, if I understand correctly: Your data is placed in 15*5=75 different worksheets? And you need to populate 5 sheets with data from one of the 15 groups?

If correctly understood, I guess your main problem is the arrangement of your data. Do all sheets have the same dimensions? If so, I guess you could set up a formulabased solution using =IF(). Whether or not the workbook can handle all the worksheets, I don't know; it would depend on available memory on your PC. But I guess it's not a feasible solution anyway, because you loose overview of the data.

But perhaps you are really looking for a VBA-solution?

\\ Søren

I think my explanation should be clearer.

Right now I have my analysis workbook in which I have 15 sheets corresponding to 1999,2000,2001,2002,2003,...2014. I also have my analyses in here, but they are of no importance now. Then I have one workbook for each target group, 15 target groups, within each target group I have 15 sheets with data corresponding to each year 1999-2014. So 15 workbooks with 15 sheets in each.
This means that when adding data I need to copy each sheet from the workbook into my analysis workbook.

In order to to simplify this process and automate it, I was thinking of a dropdown or similar that could do the trick of pasting the data I request into each sheet of the analysis workbook (1999,2000,2001).
Probably is, I have a VBA course in the end of the month - so i'll probably learn a clever way to do it at that point, I was just hoping to solve it now.

Thanks.

Ok, now I think I get it.

In that case, if you want a formulabased solution, I have 2 suggestions:
1. Set up a formula with 15 nested =IF() statements each containing a lookup formula that looks in a workbook from a dropdown menu.
2. Use =INDIRECT() to generate the appropriate formula. Be aware that =INDIRECT() is volatile, meaning that it will be recalculated every time you edit any cell in your workbook. Also, in order for =INDIRECT() to function between workbooks, all workbooks have to be open; otherwise the formula will return #REF!

Otherwise you will need a VBA solution.

I'll try your formulabased suggestions and get back to you, probably first in next week due to school.

Thank you.