# Dynamic Lists

1. ## Dynamic Lists

I'm creating a sheet that requires a certain list to appear based on a value from an adjacent cell. So for instance I have 3 lists, depending on what value appears in a certain cell will dictate which list appears in the list cell.

I have named List1 in cell A3 using values in column L, List 2 in B2 using values from column M. The variable is listed in D2, this will impact which list should appear in D3. My problem is getting the actual list to appear (drop down).

2. ## Re: Dynamic Lists

Hi
Please take a look at the workbook, and say if tis is what you're looking for..

3. ## Re: Dynamic Lists

You need to name your lists (IE, List1, List2, List3, etc)

Then in your data validation use this custom formula.

=indirect("List" & D3)

This link (http://www.contextures.com/xldataval02.html) will explain the process in full.

I recommend reading this (http://www.ozgrid.com/Excel/DynamicRanges.htm) to make each list dynamic as well.

4. ## Re: Dynamic Lists

Originally Posted by contaminated
Hi
Please take a look at the workbook, and say if tis is what you're looking for..
Yes that is what I'm looking for. Would you mind elaborating how you did this? Where is the offset formula placed? Thanks so much!

5. ## Re: Dynamic Lists

Sorry didnt see the subsequent link. Thanks again

6. ## Re: Dynamic Lists

Sure... No problem. Actually it simple enough...
Always try to put your data in a table like in range E3:J18
Then use OFFSET function.like below

=OFFSET(\$E\$3,1,MATCH(\$B\$2,\$F\$3:\$J\$3),COUNTA(OFFSET(\$E\$3,,MATCH(\$B\$2,\$F\$3:\$J\$3),1000))-1)

Lets assume that in dropdown i cell B2 3 is selected value. So..
1. MATCH(\$B\$2,\$F\$3:\$J\$3) would return position of selected value in B2 within range \$F\$3:\$J\$3. Answer would be 3
2. COUNTA(OFFSET(\$E\$3,,MATCH(\$B\$2,\$F\$3:\$J\$3),1000))-1 will count non-empty cells for selected column. This is used if don't want to dashed appear in your dropdown
3. Finally, you've got 3 for cols argument and 15 for row_height argument of OFFSET function
4. =OFFSET(E3,1,3,15)

Thus, OFFSET function will move reference from E3 to one row down, three column left and 15 rows wide. and will create range H4:H18, which will be used in dropdown source.

You will need just paste provided function into Validation dialog box (see picture)

You know, actually if you read this article or watch this tutorial or this one you will get the basic knowledge about dynamic data validation source

Note: you could also try Whizbang method (naming ranges). But what if you have 1000 and 1000s column of data... Thus you will need to name each range. It's time consuming enough..

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1