contents of dropdownlist dependant on cell value

[WilJan]

Hi all,

I'm struggling with this issue and I hope you can help me out:

• In a table on sheet 1, I want to show a dropdown list in cell H3 using datavalidation. The contents of the list should be dependent on the value in cell G3. If G3 contains the abbreviation “KS” the contents of the list should be based on the named range “KS”, which refers to the range =OFFSET(Sheet2!$I$2;1;0;COUNTA(Sheet2!$I:$I)-1;1). If G3 contains “OT” it should load that named range into the list, etc. I referred to these named ranges elsewhere and they seem to work.
• To make the dropdown dependent on the value in G3 I inserted the formula =Indirect(G3) as the source for the list in the datavalidation screen
• This results in a blanc list, though

thank you so much!

Wilco

 

[Georgiboy]

Does the cell G3 contain any leading spaces or anything that would make it different from the named ranges.

Have you tried:

=INDIRECT(TRIM(G3))

 

[WilJan]

Does the cell G3 contain any leading spaces or anything that would make it different from the named ranges.

Have you tried:

=INDIRECT(TRIM(G3))

not yet, thank you, but that doesn't seem to solve the problem i'm afraid

 

[Georgiboy]

Does the cell G3 contain any other characters other than: KS or OT?

 

[WilJan]

Does the cell G3 contain any other characters other than: KS or OT?

no, none, just the abbreviations. I've tried a few things in the meantime and apperently there's something wrong with the named range after all, if i remove the offset-part and just enter I3:I5 it works correctly (but it needs to be dynamic to allow for new entries)

 

[Georgiboy]

You could create tables for your dropdowns and name the table after: KS & OT etc...
Then you could still use: =INDIRECT(A3) for example, that way the tables will extend when you add new values.

Tables on the right are named: KS, OT & BP

Book1
	A	B	C	D	E	F	G	H
1	Abbrv	DDown				KS	OT	BP
2	KS					1	4	1
3	OT					2	5	2
4	KS					3	6	3
5	OT						7
6	BP
7
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B2:B6	List	=INDIRECT(A2)

 

[Akuini]

no, none, just the abbreviations. I've tried a few things in the meantime and apperently there's something wrong with the named range after all, if i remove the offset-part and just enter I3:I5 it works correctly (but it needs to be dynamic to allow for new entries)

Indirect can't use a 'dynamic' named range.
See if this helps:
range-name-using-offset-formula-vs-data-validation