Dynamic List Across Column Range

[gnaga]

Hi,

I am trying to generate a Dynamic List across column range based on a cell value. When I check the below formula by highlighting it and pressing Ctrl + F9 (I don't know what is it called.....Debugging????) I am getting the three list of values correctly. But when I create a name for this formula in Name Manager and use that name in my Data Validation it says "The Source currently evaluates to an error. Do you want to continue". Could some one give me some direction to sort this out please?

=OFFSET(tblSample[[#Headers],[Sample ID]],MATCH(Sheet1!$H3,tblSample[[#All],[Sample ID]],0)-1,2,1,3)

In the above formula I am generating a Dynamic List Based on value in Cell Sheet1!$H3.

 

[gnaga]

Book 1.xlsx
	B	C	D	E	F	G	H	I	J	K	L
7
8		ID	Desc	A1	A2	A3			ID	A
9		10	ABC	Tuesday	Monday				10	#VALUE!
10		20	DEF	Tuesday		Wednesday
11		30	GHI		Monday	Wednesday
12		40	JKL	Tuesday	Monday	Wednesday
13		50	MNO			Wednesday
14		60	PQR		Monday
15		70	TUV	Tuesday
16
17
Sheet3

Cell Formulas
Range	Formula
K9	K9	=OFFSET(Table7[[#Headers],[ID]],MATCH($J9,Table7[[#All],[ID]],0)-1,2,1,3)

Cells with Data Validation
Cell	Allow	Criteria
J9:J15	List	=$C$9:$C$15
K10	List	=Find_A

The above is my example Data and the Formula in K9 is working fine when I do test by pressing Ctrl +F9. But when I make that formula as Named Range and then use that Name for Data Validation it is not accepting.

 

[gnaga]

In my last two posts I mentioned as Ctrl + F9 which is incorrect. I select the Formula and Press only F9 and it shows the correct out put in the Formula Bar---Just to correct my information

 

[gnaga]

See the result in Formula Bar, when I press F9 by selecting the Formula in Cell K9. It producing the list but when the formula goes into Named Range it is not accepting the Name is not accepting as Data Validation Source Range. Hope my issue is clearly explained.

 

[gnaga]

Hope my question and issue is clear to all.