Dynamic drop-down box
Experts:
I have a dynamic drop-down menu. Need to see if someone can offer some ideas how to make it more customized.
Here's what I have:
- On tab "Lookups", I've entered values (e.g., "A", "B", ..., ..., "F") in cell range A2:A7.
- I then named that cell range, e.g., "RecordSet"
- Finally, on another worksheet, I selected "List" and then entered "=RecordSet" as the source.
Thus, the drop-down displays A through F on my actual data tab.
Now, here's what I'd like to modify.
- On the Lookup tab, I'd like to use column "B" as a helper column. Let's say, I use values of either "1" and "2" for distinction.
- Thus, I may have the following in cell range A2:B7:
A2: A 2
A3: B 2
A4: C 1
A5: D 2
A6: E 2
A7: F 1
Now, my drop-down menus should now list the following:
A
B
D
E
and
C
F
Above information is quite simplied... as you can imagine, I use different value sets for the actual spreadsheet.
Anyhow, point is that I prefer NOT to create several name ranges... which would easily solve the problem.
Instead though, I prefer to have the "1" or "2" (in actuality, there will be probably 6 values to "drive" under which drop-down menu a value should be listed (and values must be sorted on column A... not column B).
Please see attached XLS for "current" vs. "envisioned" process.
Any suggestions as to how this can be accomplished?
Thanks,
EEH
Dynamic-Drop-Down.xlsx
I have a dynamic drop-down menu. Need to see if someone can offer some ideas how to make it more customized.
Here's what I have:
- On tab "Lookups", I've entered values (e.g., "A", "B", ..., ..., "F") in cell range A2:A7.
- I then named that cell range, e.g., "RecordSet"
- Finally, on another worksheet, I selected "List" and then entered "=RecordSet" as the source.
Thus, the drop-down displays A through F on my actual data tab.
Now, here's what I'd like to modify.
- On the Lookup tab, I'd like to use column "B" as a helper column. Let's say, I use values of either "1" and "2" for distinction.
- Thus, I may have the following in cell range A2:B7:
A2: A 2
A3: B 2
A4: C 1
A5: D 2
A6: E 2
A7: F 1
Now, my drop-down menus should now list the following:
A
B
D
E
and
C
F
Above information is quite simplied... as you can imagine, I use different value sets for the actual spreadsheet.
Anyhow, point is that I prefer NOT to create several name ranges... which would easily solve the problem.
Instead though, I prefer to have the "1" or "2" (in actuality, there will be probably 6 values to "drive" under which drop-down menu a value should be listed (and values must be sorted on column A... not column B).
Please see attached XLS for "current" vs. "envisioned" process.
Any suggestions as to how this can be accomplished?
Thanks,
EEH
Dynamic-Drop-Down.xlsx
ASKER
NB_VC:
Thanks for the prompt reply....
Unfortunately, the name range in the lookup data will be sorted on column A in ASC order.
Besides, "1" and "2" categories may be changed frequently.
Based on the original post, do you think this is still possible?
Thanks,
EEH
Thanks for the prompt reply....
Unfortunately, the name range in the lookup data will be sorted on column A in ASC order.
Besides, "1" and "2" categories may be changed frequently.
Based on the original post, do you think this is still possible?
Thanks,
EEH
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
NB_VC:
Wow... I'm totally impressed. Your proposed solution is brilliant!!!
Using your concept, I've created different sections on "Sheet1" to identify either "1", "2", and "3" (new).
For testing purposes, I changed values in range B2:B7 on the Lookups tab.... whatever the combination across the three identifiers, the drops-down change accordingly.
Again, this solution is perfect!!!
Thousands thanks.
EEH
Wow... I'm totally impressed. Your proposed solution is brilliant!!!
Using your concept, I've created different sections on "Sheet1" to identify either "1", "2", and "3" (new).
For testing purposes, I changed values in range B2:B7 on the Lookups tab.... whatever the combination across the three identifiers, the drops-down change accordingly.
Again, this solution is perfect!!!
Thousands thanks.
EEH
ASKER
NB_VC:
Quick follow-up question.
*****
Now data validation formula is:
=OFFSET(RSet,1,0,COUNTIF(R
*****
While above offset function works great, it still permits any value to be entered by a user.
As you know, when using a list reference (.e.g., name) any other value outside the defined list values will throw an error message... thus forcing members to choose a value from the drop-down.
Apparently, this is not the case w/ the offset function. Basically, if users are unfamiliar with the drop-down method (I'm sure there are many), a similar but different values may be selected. If so, corresponding VLookup formula won't populate adjacent cells/columns.
Is there any way I can force users to select values via drop-down with Offset reference?
Thanks,
EEH
Quick follow-up question.
*****
Now data validation formula is:
=OFFSET(RSet,1,0,COUNTIF(R
*****
While above offset function works great, it still permits any value to be entered by a user.
As you know, when using a list reference (.e.g., name) any other value outside the defined list values will throw an error message... thus forcing members to choose a value from the drop-down.
Apparently, this is not the case w/ the offset function. Basically, if users are unfamiliar with the drop-down method (I'm sure there are many), a similar but different values may be selected. If so, corresponding VLookup formula won't populate adjacent cells/columns.
Is there any way I can force users to select values via drop-down with Offset reference?
Thanks,
EEH
Uncheck the checkbox in the Settings tab that states "Ignore blank"... that should now disallow users to enter random text.
ASKER
Works like a charm.... thanks again!
I cannot see a clean way without creating other tables that reference that table in order to group the data.