Dynamic Drop Down List 1

[kshanes]

I've always been able to find a resolution to my problems on this site, but for some reason can't seem to find a solution to the following....

In excel, I have a range of cells (i.e. F1 to F26) with various unique entries (i.e. the letters A to Z). I then have cells A1 to A26 reference this list of cells for a drop down list (List Validation). What I need to do is if in Cell A1 the user choose "B" from the Validation list, then "B" should be removed from the list of available options for the drop down in cell A2. Does anyone have any suggestions? (I hope this example makes sense)

This is probably really easy and I'm know I could somewhat easily do it in a programming language, but I'm sure there's an easy mechanism in excel rather than me writing a VB or Java program to do it.

Thanks in advance for any suggestions you may have.

~K

 

[DaleWatson123321]

Hi kshanes,

Thanks for the challenge.

I've created a file that accomplishes your task. It doesn't use VBA, and I believe it's exactly what you're seeking.

Probably the easiest way of communicating what I've done, is for me to email you the file. So if you'd like the file, simply email me and I'll send the file via return email.

For others who might also be interested in the solution, you're also welcome to the file.

For those who just want to read about the approach I used, here are the steps...

1) For the data in F1:F26, I assigned the range name "alpha".

2) In cell A1, I used Data - Validation. Under "Allow", specified "List", and under "Source", specified "=alpha".

3) Copied cell A1 to A1:A26

4) In G1:G26, I created a duplicate of the data that would normally be in F1:F26 (i.e. A to Z)

5) In H1, I entered this formula:
=IF(ISNA(VLOOKUP(G1,A_list,1,FALSE)),"",VLOOKUP(G1,A_list,1,FALSE))

(If the value is NOT found in "A_list", then the formula returns BLANK, otherwise returns the Letter.)

6) Copied cell H1 to H1:H26

7) In F1, I entered the following formula:
=IF(G1=H1,"","A&quot

(If G and H are the same, show BLANK, otherwise show the Letter.)

8) Copied cell F1 to F1:F26

9) Because the formula copied to F1:F26 has a "hard-coded" value for the Letter, it requires editing F2:F26 and changing the Letter. For example, cell F2 needs to be changed from:
=IF(G2=H2,"","A&quot to =IF(G2=H2,"","B&quot

That's all. Now the Data Validation cells in A1:A26 will eliminate from the dropdown list, any value selected from that same list.

I hope this is what you were expecting. Please advise as to how it fits.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

In reviewing my steps, I realized I didn't include a step for assigning the range name "A_list" - referred to by the formula in Step #5.

The range name "A_list" needs to be assigned to the range: A1:A26.