auto populate cells based on drop-down selection

0
votes

I have a spreadsheet that I would like to be able to auto-populate "lists" based on a drop-down selection. For example, A1 has a drop-down menu for all the departments in my company. We have different "to-do" list for each department (inserted through multiple rows on a second sheet). Is there a way to easily make it so that If I choose the (i.e.) HR department, the HR to-do list will auto-populate on the rows B7 and on, or if I choose the IT department, then the IT to-do list will auto-populate? I know I can do it with vlookup, but since each department has a different list, with different amount of items, I'm trying to find an easier way.

Thanks in advance!

1
excelvba
You can use the worksheet_change event to trigger fetching the relevant list based on the selected value - try that, then post back with your code if you run into specific problems. – Tim Williams
Thank you! I still couldn't figure out who to get it to work so I ended up using a huge IF formula to return range. – user14138444

1 Answers

0
votes

Here is a simple example based on MATCH() and INDEX()

say the categories are in column A and the data validation pull-down that references these categories is in cell B1.

Associated with each category is a list and the lists are located in a table in columns D through F:

enter image description here

In C1 enter the formula:

=INDEX($D$1:$F$4,ROWS($1:1),MATCH($B$1,A:A,0))

and copy downward.

Then when we select a category in B1, the associated list will appear in column C:

enter image description here