On Nov 13, 9:21 am, markmcd wrote:
How do I get a selection via a vlookup formula? I have several columns of
data with data validation. In the first column is the choice Rigid, Prime
Mover or Tanker. In another column, the choices are for Rigid choice 1 and 2,
for Prime Mover the choices need to be 1, 2, 3, 4 and for Tanker the only
choice is to be Not Applicable. If I select Rigid, I don't want to see the
options for Prime Mover or Tanker. I have created a table of data like so:

Prime Mover 1
Prime Mover 2
Prime Mover 3
Prime Mover 4
Rigid 1
Rigid 2
Tanker Not applicable

The numbers are only for ease of demonstration, the data is actually text.
Is it possible to look up Prime Mover (via vlookup) to then determine which
choices should be made available. I have read Debra Dagleish's document on
data validation but it doesn't state this situation very clearly. Any help
appreciated.

I have a solution for you, but there are maybe better solutions out
there.

you have to spare 3 columns, let's say colums "IT" "IU" and "IV"
In column A you have your choices "Prime Mover", "Rigid" and "Tanker"
in Column B you want to select according to your choices in A
In Column "IT" you put following formula: =IF(A1="",6,IF(A1="Tanker",
5,1))
In Column "IU" following: =IF(A1="Prime Mover",2,IF(A1="Rigid",
4,IF(A1="Tanker",5,6)))
copy those formulas down.
Now in column "IV" you enter those values:
IV1 = 1
IV2 = 2
IV3 = 3
IV4 = 4
IV5 = not applicable

now the data validation formula in Column "B" should look like this:
=INDIRECT("IV" & IT1 & ":IV" & IU1)

Hope that is clear, but as I told you before, i believe that there are
better solutions
out there. But It works, and maybe you can use it.

Carlo