Indirect Formula Error

[decent_boy]

Hi
I am using indirect formula for dependent drop down list in mentioned below data

Dependent-Drop-Down-List-Excel_v2.xlsx
	A	B	C	D	E	F	G
1	Fruits	Vegetables
2	Mango	Potato		Drop Down 1	Drop Down 2		Formula
3	Apple	Tomato		Vegetables	Tomato		Tomato
4	Grapes	Spinach
5	Strawberry	Onion
6	Kiwi	Cucumber
Dependent Drop Down

Cell Formulas
Range	Formula
G3	G3	=INDIRECT($D3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3	Expression	=ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)),1,0))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D3	List	=$A$1:$B$1
E3	List	=INDIRECT($D$3)

But When I change vegetable name to Alan indirect function show error and does not work, can anybody help me on this

Dependent-Drop-Down-List-Excel_v2.xlsx
	A	B	C	D	E	F	G
1	Fruits	Alan
2	Mango	Potato		Drop Down 1	Drop Down 2		Formula
3	Apple	Tomato		Alan			#REF!
4	Grapes	Spinach
5	Strawberry	Onion
6	Kiwi	Cucumber
Dependent Drop Down

Cell Formulas
Range	Formula
G3	G3	=INDIRECT($D3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3	Expression	=ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)),1,0))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D3	List	=$A$1:$B$1
E3	List	=INDIRECT($D$3)

 

[Fluff]

Do you have a named range called Alan?

 

[decent_boy]

Do you have a named range called Alan?

No

 

[Joe4]

As Fluff alluded to, I think that INDIRECT is working off of named ranges, not column titles.
You probably have a named range named "Vegetables", but not one named "Alan", which would explain your issue.

 

[decent_boy]

If I do not want to create name list then how can it be done without named range so when I change it, it will not show error

 

[Joe4]

The INDIRECT function must use a valid cell/range reference, which would be an explicit cell/range address, or a named range.
(The following taken from Microsoft Help on INDIRECT function: INDIRECT function - Microsoft Support)

Those appear to be the only two options when using the INDIRECT function to refer to a range on your sheet.
1. hard-coded address
2. named range

 

[Joe4]

Note that you can use different methods if you set up your lists as actual tables.
See: Excel Tables as Source for Data Validation Lists

 

[Fluff]

You could try

=INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1,0))

 

[decent_boy]

You could try

=INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1,0))

Thank you so much Fluff

 

[decent_boy]

The INDIRECT function must use a valid cell/range reference, which would be an explicit cell/range address, or a named range.
(The following taken from Microsoft Help on INDIRECT function: INDIRECT function - Microsoft Support)

View attachment 94439

Those appear to be the only two options when using the INDIRECT function to refer to a range on your sheet.
1. hard-coded address
2. named range

That is great and thanks so much for explaining of it.