Dynamic Population of Data Validation Lists

Have a look at the thread that starts with

8720.html

because your question somehow sounds similar to me.

Aladin

Thanks for the response Aladin - but it's not a dynamic solution. In your suggestion, you still have to hardcode the ranges to pick up for each Division value. With my data, I don't know what the ranges will be, as the data is updated on a regular basis and involves thousands of rows. Based on a supplied code, I want to pick up every name associated with that code in the range lookup.

Vince

OK Vince. There is thus some smilarity. Because you don't know beforehand what a given range will be, you're concerned about hard-coding that seems to be necessary. That's not that big a problem. If you know the cell a given range begins, it's not difficult at all to compute dynamically where it ends.
Back to your problem.
What I don't understand about it, the way you describe your possible ranges. What does mean,

"If Col A has a value of A2, then the drop down list should display Bill, Bob and Brad"?

Care to explain this? Is this A2 a value or the cell A2? You may also send in a snippet of you data, if you wish.

Aladin

Sorry for the confusion. If the lookup data looked as follows:
AP1 Tom
AP1 Dan
AP2 Bill
AP2 Bob
AP2 Brad
AP3 Jen
AP3 Jill

Then if a user entered a code of AP2 in cell A1, then cell B1 should have a drop-down list containing Bill, Bob and Brad. The next time that value was entered, there may be a different number of names associated with code AP2.

Hi Vince

Here we go.

I assume the codes are in A on Sheet1 and the names in B. A1 contains the label Code and B1 the label Name.
What follows must be done on Sheet1.

In D2 enter: =ADDRESS(ROW(A2), COLUMN(A2)) & ":" & ADDRESS(COUNTA(A:A), COLUMN(A2))
In D3 enter: =ADDRESS(ROW(B2), COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2))
In D4 enter: =ADDRESS(ROW(E2), COLUMN(E2)) & ":" & ADDRESS(COUNTA(E:E)+1, COLUMN(E2))
In D5 enter: =ADDRESS(ROW(F2), COLUMN(F2)) & ":" & ADDRESS(D7, COLUMN(F2))
In D7 enter: =COUNTA(A:A)

On Sheet2 name cell A1 "InputCode" via the Name Box or via the option Insert, Name, Define.

Back to Sheet1.

In E2 enter: =IF(INDIRECT($D$2)=InputCode, INDIRECT($D$3), 0), go to E2 after typing the formula and select, say, 25 cells from E2 on, then go to the formula bar and hit CONTROL+SHIFT+ENTER at the same time. As a result, you'll see names to appear in some or in all cells of the range E2:E25.

In F2 enter: =IF(ROW()-ROW(INDIRECT($D$5))+1 > ROWS(INDIRECT($D$4))-COUNTIF(INDIRECT($D$4),0), "", INDIRECT(ADDRESS(SMALL(( IF(INDIRECT($D$4)<>0,  ROW(INDIRECT($D$4)), ROW() + ROWS(INDIRECT($D$4)))), ROW() - ROW(INDIRECT($D$5)) + 1), COLUMN(INDIRECT($D$4)), 4))), go to F2 after typing the formula and select, say, 25 cells from F2 on, then go to the formula bar and hit CONTROL+SHIFT+ENTER at the same time. As a result, you'll see names from E2:E25 to appear in top cells or in all cells of the range F2:F25.

Activate the option Insert,Define,Name, type DynDDList for Names in workbook and =OFFSET(Sheet1!$F$2, 0, 0, COUNTA(Sheet1!$F:$F), 1) for Refers to. Click OK.

Back to Sheet2.

Select B1, activate the option Data,Validation and on Settings tab choose List for Allow, then type =DynDDList for Source. Click OK.

Note. The number 25 refers to max number of names that you expect to populate any dropdown list.

Caveat. It is impossible to exclude "blanks" from the dropdown lists. That is, I didn't find a way to exclude them! [ As a side: "Ignore blank" on Settings tab of Data Validation appears to me a useless option. It just means "empty", not "blank" or "". ]

I hope I didn't forget something. Can send you the workbook containing the above machinery if you wish.

Aladin

VINCE: The procedure that I described in the previous post is not fully automatic. Moreover, I made a mistake in describing one of the steps. I'll correct that after giving you a better system of formulas that follows. This one produces dropdown lists that contains just names, no blanks.

I (still) assume the codes are in A on Sheet1 and the names in B. A1 contains the label Code and B1 the label Name.

What follows must be done on Sheet1.

In D2 enter: =ADDRESS(ROW(A2), COLUMN(A2)) & ":" & ADDRESS(COUNTA(A:A), COLUMN(A2))

In D3 enter: =ADDRESS(ROW(B2), COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2))

In D4 enter: =ADDRESS(ROW(E2), COLUMN(E2)) & ":" & ADDRESS(COUNTA(E:E)+1, COLUMN(E2))

In D5 enter: =ADDRESS(ROW(F2), COLUMN(F2)) & ":" & ADDRESS(D6, COLUMN(F2))

In D6 enter: =COUNTA(A:A)

On Sheet2 name cell A1 "InputCode" via the Name Box or via the option Insert, Name, Define.

Back to Sheet1.

In E2 enter: =IF(ISBLANK(A2),0,IF(ROW()-1<=$D$6, IF(INDIRECT($D$2) = InputCode, INDIRECT($D$3),0),0)) [ copy down as far as needed -- that is, copy down as far as expected number of rows with codes and names ]

In F2 array-enter: =IF(ROW()-ROW(INDIRECT($D$5))+1 > ROWS(INDIRECT($D$4))-COUNTIF(INDIRECT($D$4),0), 0, INDIRECT(ADDRESS(SMALL(( IF(INDIRECT($D$4)<>0, ROW(INDIRECT($D$4)), ROW() + ROWS(INDIRECT($D$4)))), ROW()-ROW(INDIRECT($D$5)) + 1), COLUMN(INDIRECT($D$4)),4))) [ copy this array-formula down to 25 rows, which is your expected number of different names associated with any code ]

To array-enter: hit CONTROL+SHIFT+ENTER at the same time instead of just hitting ENTER.

(By the way, I gave a wrong description of this step in the previous post -- now an irrelevant point.)

Activate the option Insert,Define,Name, type DynDDList for Names in workbook and =OFFSET(Sheet1!$F$2, 0, 0, COUNTIF(Sheet1!$F$2:$F$25, "<>0"),1) for Refers to. Click OK.

Back to Sheet2.

Select B1, activate the option Data,Validation and on Settings tab choose List for Allow, then type =DynDDList for Source. Click OK.

Caveat. Data range of codes and names must be sorted.

I'm sending you the workbook containing the above machinery.

Cheers.

Aladin