Excel Question - Data Validation and Dependent Variables

[The Glyphstone]

Trying to do something a little complex in Excel, hopefully someone can help me.

I've got 2 sets of variables...let's say [A,B,C,D] and [1,2,3,4]. I've figured out how to validate data from a list, so in Cell 1, I can make a dropdown menu that lets a user pick from A, B, C, or D. What I want to do is set up a function so that Cell 2 fills itself in with the corresponding dependent value...if they pick A, it returns 1, if they pick B it returns 2, etc....while rejecting any manual input. How do I do this?

[TuggyNE]

Use a formula in the protected cells; assuming the list of matches is in D1:E4 with letters on the left, and the drop-down is in A1, something like this:
=VLOOKUP(A1,$D$1:$E$4,2,FALSE)
Then, use Custom validation with the formula =False to forbid any changes to the formula cells; formulas don't care about validation.

Hopefully this is clear enough, but if not I can try to explain more fully. :smallwink:

[The Glyphstone]

Use a formula in the protected cells; assuming the list of matches is in D1:E4 with letters on the left, and the drop-down is in A1, something like this:
=VLOOKUP(A1,$D$1:$E$4,2,FALSE)
Then, use Custom validation with the formula =False to forbid any changes to the formula cells; formulas don't care about validation.

Hopefully this is clear enough, but if not I can try to explain more fully. :smallwink:

Yeah...I've almost got it, but I think a bit more detail might be needed.

So, Drop-down menu is in A1, and the cell with the return value is A2. The four values the Dropdown is allowed to pick from are in D1,D2,D3,and D4. The four corresponding return values are in E1, E2, E3, and E4.

EDIT: Never mind, that did work. I was punching in bad variables and getting bad returns. Now to see if I can duplicate it, I.e. have A3 read from F1-F4 based on A1's value.

EDIT2: Nope. So, your first explanation worked like a charm. Now I'm trying to do the above, get 2 sets of dependent variables reading from one dropdown (still in D1-D4, let's leave the first set of dependents in E1-E4 and put the second set in F1-F4, or move them if needed.)

[TuggyNE]

EDIT2: Nope. So, your first explanation worked like a charm. Now I'm trying to do the above, get 2 sets of dependent variables reading from one dropdown (still in D1-D4, let's leave the first set of dependents in E1-E4 and put the second set in F1-F4, or move them if needed.)

Excellent! :smallcool: The key thing is to change the numeric parameter of VLOOKUP (which sets the column offset to use) from 2 to 3 in this second formula set. The rest should be fine with normal changes to references.


=VLOOKUP(A1,$D$1:$F$4,3,FALSE)

Although it occurs to me that making the first reference $A1 in both formulae might be wise. I'm a paranoid feller like that.

[The_Ditto]

Personally, I've fallen in love with OFFSET when I do my work, and using that, I routinely build "dynamic" drop lists.

When building complex drop lists, I typically use the following process - which should work for just about any kind of logic needed.

a) often lists are based off an initial static list, so setup a new sheet, and setup those static lists first. Name them. (Named ranges = good).
b) next, if needed, add logic to filter that static list based on whatever criteria you have. (for example, let's use D&D as a sample, if you have a list of Classes, and want to limit the selection based on Ability requirements ala 2nd ed, or such, you can do your formulas/etc and "build" a second list alongside the first.)
c) Name it, however, since it's dynamic, you need to be a bit tricky:
(I usually use 2 columns for this just to make things easier. Column "B" has the actual (final) list (no blanks in between)). Cell "A1" would have formula "=Count(B:B)"
d) Name Cell A1 as "cntClassList" or something.
Name Cell B1 as "hdrClassList" (and text would be static "Class" - just a header - actual list data starts at B2)
e) Range would be named "lstClassList" and formulas is: "=offset(hdrClassList,1,0,cntClassList,1)"
Then in your original sheet, setup Data Validation with "List" and formula:
"=lstClassList".

I've used this method over and over with various degress of complex list creation with great success. :)

Hope it helps.

[edit]
here's a sample little .xls to demonstrate this ...
class_test.xls (https://sites.google.com/site/dotditto/class_test.xls?attredirects=0&d=1)

I've used this method in both my 3.5 and 4e character sheets with great success :)
[edit]

[The Glyphstone]

Excellent! :smallcool: The key thing is to change the numeric parameter of VLOOKUP (which sets the column offset to use) from 2 to 3 in this second formula set. The rest should be fine with normal changes to references.


=VLOOKUP(A1,$D$1:$F$4,3,FALSE)

Although it occurs to me that making the first reference $A1 in both formulae might be wise. I'm a paranoid feller like that.

I'm making an note here, Huge Success.

Thanks for the help...I'll just stick with this method, all these complicated things with Indexes and Offsets is probably more than I need.

[TuggyNE]

Thanks for the help...I'll just stick with this method, all these complicated things with Indexes and Offsets is probably more than I need.

And for the first time in recorded history, one of TuggyNE's solutions was not horrifically overengineered. :smallamused:

Happy to help!