Data Validation Drop Down List using INDIRECT and VLOOKUP

[SteveAB]

Hi All,
This is my first time on the forum and hope everyone is enjoying the new year.
I am using Excel 2016 and creating an estimating workbook. To simplify my question I'll use the following example.

I have created a table called Inventory as shown below.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CATEGORY[/TD]
[TD]ITEM[/TD]
[TD]UNIT[/TD]
[TD]PRICE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bolts[/TD]
[TD]25x10mm bolt[/TD]
[TD]Each[/TD]
[TD]$0.20[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bolts[/TD]
[TD]50x10mm bolt[/TD]
[TD]Each[/TD]
[TD]$0.30[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Nuts[/TD]
[TD]10mm nut[/TD]
[TD]Each[/TD]
[TD]$0.15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Washer[/TD]
[TD]10mm flat washer[/TD]
[TD]Each[/TD]
[TD]$0.10[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Washer[/TD]
[TD]10mm split washer[/TD]
[TD]Each[/TD]
[TD]$0.12[/TD]
[/TR]
</tbody>[/TABLE]

I have created another table called Estimate shown below
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Category[/TD]
[TD]Item[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bolt[/TD]
[TD]Drop down list
showing choices of bolts from Inventory table
[/TD]
[/TR]
</tbody>[/TABLE]

I'm trying to achieve a drop down list to give me choices for the category selected. In this example, the category is "Bolt".

To try and achieve this I have used the following formula in Data Validation source.
=INDIRECT(VLOOKUP(F2,A1:D5,2,FALSE))
Unfortunately my logic doesn't work
Any suggestions?

Cheers
Steve

 

[SteveAB]

Apologies. F2 should read Bolts. Not Bolt

 

[T. Valko]

Like this...

Data Range
[Table="class: grid"][tr][td="bgcolor: #c0c0c0"][/td][td="bgcolor: #c0c0c0"]

A​

[/td][td="bgcolor: #c0c0c0"]

B​

[/td][td="bgcolor: #c0c0c0"]

C​

[/td][td="bgcolor: #c0c0c0"]

D​

[/td][td="bgcolor: #c0c0c0"]

E​

[/td][td="bgcolor: #c0c0c0"]

F​

[/td][td="bgcolor: #c0c0c0"]

G​

[/td][/tr]
[tr][td="bgcolor: #c0c0c0"]

1​

[/td][td]

CATEGORY​

[/td][td]

ITEM​

[/td][td]

UNIT​

[/td][td]

PRICE​

[/td][td]

------​

[/td][td]

CATEGORY​

[/td][td]

ITEM​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

2​

[/td][td]

Bolts​

[/td][td]

25x10mm bolt​

[/td][td]

Each​

[/td][td]

$0.20​

[/td][td][/td][td]

Bolts​

[/td][td]

25x10mm bolt​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

3​

[/td][td]

Bolts​

[/td][td]

50x10mm bolt​

[/td][td]

Each​

[/td][td]

$0.30​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

4​

[/td][td]

Nuts​

[/td][td]

10mm nut​

[/td][td]

Each​

[/td][td]

$0.15​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

5​

[/td][td]

Washer​

[/td][td]

10mm flat washer​

[/td][td]

Each​

[/td][td]

$0.10​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

6​

[/td][td]

Washer​

[/td][td]

10mm split washer​

[/td][td]

Each​

[/td][td]

$0.12​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

As the source for the drop down list in G2 use this formula:

=OFFSET(A1,MATCH(F2,A:A,0)-1,1,COUNTIF(A:A,F2))

 

[SteveAB]

Thanks very much. It works great.

 

[seekerarcane]

This would also be helpful.

http://www.contextures.com/xlDataVal02.html

 

[SteveAB]

Yes, the dependent drop down lists work well.
This one was for INDIRECT VLOOKUP. I also found one using INDIRECT MATCH which seems to be a better way of doing things. I suppose it depends on the context.

 

[T. Valko]

You're welcome! Thanks for the feedback!