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.

	A	B	C	D
	CATEGORY	ITEM	UNIT	PRICE
1	Bolts	25x10mm bolt	Each	$0.20
2	Bolts	50x10mm bolt	Each	$0.30
3	Nuts	10mm nut	Each	$0.15
4	Washer	10mm flat washer	Each	$0.10
5	Washer	10mm split washer	Each	$0.12

<tbody>
</tbody>

I have created another table called Estimate shown below

	F	G
1	Category	Item
2	Bolt	Drop down list showing choices of bolts from Inventory table

<tbody>
</tbody>

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

	A​	B​	C​	D​	E​	F​	G​
1​	CATEGORY​	ITEM​	UNIT​	PRICE​	------​	CATEGORY​	ITEM​
2​	Bolts​	25x10mm bolt​	Each​	$0.20​		Bolts​	25x10mm bolt​
3​	Bolts​	50x10mm bolt​	Each​	$0.30​
4​	Nuts​	10mm nut​	Each​	$0.15​
5​	Washer​	10mm flat washer​	Each​	$0.10​
6​	Washer​	10mm split washer​	Each​	$0.12​

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!