Data Validation on Vlookup

[Guest]

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.

 

[Gord Dibben]

Don't know which section of Debra's site you read but for dependent lists see
this site.

http://www.contextures.on.ca/xlDataVal13.html

Explains it quite clearly IMO

You may have to adjust your data layout to achieve your ends.


Gord Dibben MS Excel MVP

 

[carlo]

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

 

[Guest]

Hi Carlo,

Thanks for this. It works. Extending on this idea I have another list
whereby if the selection in column A is Prime Mover then options should be
50, 70, 110, 130, or "by Supplier"
For the rigid, the options should be 10,20,45 or 50. For tanker the only
option should be Not applicable. So the overall list should be
10
20
45
50
70
110
130
By Supplier
Not applicable

From your previous example, everything worked well but the ranges all began
from the top row. Here the ranges are different. ie Semi should only address
50 and upwards but Rigid only up to 50. I'm able to get it to work but under
Rigid the choices are 10,20,45,50 which is fine but the Semi also starts from
10 when it should be 50 all the way to the end of the list excepting Not
Applicable which only applies to Tanker.

Any ideas much appreciated.

 

[carlo]

Hi Carlo,

Thanks for this. It works. Extending on this idea I have another list
whereby if the selection in column A is Prime Mover then options should be
50, 70, 110, 130, or "by Supplier"
For the rigid, the options should be 10,20,45 or 50. For tanker the only
option should be Not applicable. So the overall list should be
10
20
45
50
70
110
130
By Supplier
Not applicable

From your previous example, everything worked well but the ranges all began
from the top row. Here the ranges are different. ie Semi should only address
50 and upwards but Rigid only up to 50. I'm able to get it to work but under
Rigid the choices are 10,20,45,50 which is fine but the Semi also starts from
10 when it should be 50 all the way to the end of the list excepting Not
Applicable which only applies to Tanker.

Any ideas much appreciated.










- Show quoted text -

Hi

ok, in that case you need to change the Formula in "IT".
Let's say your Values start in IV1 in the following order:
10
20
45
50
70
110
130
By Supplier
Not applicable

then you should following formula in "IT"
=IF(A1="Prime Mover",4,IF(A1="Rigid",1,IF(A1="Tanker",9,10)))
and in "IU"
=IF(A1="Prime Mover",8,IF(A1="Rigid",1,IF(A1="Tanker",9,10)))

just so that you can make your own adjustments:
"IT" tells you the start cell, "IU" the end cell.
In my first post I had a different formula for "IT" because it was an
easier case. Now that the case changed, I took the same formula
like in "IU".
The true-value of the IFs is always the start- or endcell to the
according
Value. The last else is an empty row, so you don't get any errors.

hth

Carlo

 

[Guest]

Thanks Carlo...this has done the job. I'll study this a little further so I
can get my head around what's going on here.

 

[carlo]

You're welcome.
If you have any questions, just post them here!

Carlo