selecting a named range

[MalcNZ]

Hi Team
first time poster here. My query is this, I want to select a named range (three cells wide) that contains P/O number, product type and quantity. I can bring up the "Name" of the range with Data validation but I'm stuck as to getting it to list the contents of the range.

Thanks in advance for any assistance.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
3
4										list contents here
5		P/o	Type	Num			_1234
6		1234	apples	10
7		2345	pears	15
8		3456	oranges	20
9		4567	bananas	25
10
11
12
13							_1234	=Sheet1!$B$6:$D$6
14							_2345	=Sheet1!$B$7:$D$7
15							_3456	=Sheet1!$B$8:$D$8
16
17
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
G5	List	=NamedRange

 

[rlv01]

One way is to use the Index function.

Book1
	B	C	D	E	F	G	H	I	J	K	L
2									list contents here
3	P/o	Type	Num					1234	apples	10
4	1234	apples	10
5	2345	pears	15
6	3456	oranges	20
7	4567	bananas	25
Sheet1

Cell Formulas
Range	Formula
I3	I3	=INDEX(NamedRange,1,1)
J3	J3	=INDEX(NamedRange,1,2)
K3	K3	=INDEX(NamedRange,1,3)

Named Ranges
Name	Refers To	Cells
NamedRange	=Sheet1!$B$4:$D$4	I3:K3

 

[MalcNZ]

One way is to use the Index function.

Book1
	B	C	D	E	F	G	H	I	J	K	L
2									list contents here
3	P/o	Type	Num					1234	apples	10
4	1234	apples	10
5	2345	pears	15
6	3456	oranges	20
7	4567	bananas	25
Sheet1

Cell Formulas
Range	Formula
I3	I3	=INDEX(NamedRange,1,1)
J3	J3	=INDEX(NamedRange,1,2)
K3	K3	=INDEX(NamedRange,1,3)

Named Ranges
Name	Refers To	Cells
NamedRange	=Sheet1!$B$4:$D$4	I3:K3

Thank you!, i will try that.

 

[MalcNZ]

Thank you!, i will try that, not sure how i can tie that to the data validation G5?

 

[rlv01]

Perhaps something like this.

Book1
	B	C	D	E	F	G	H	I	J	K
2									list contents here
3	P/o	Type	Num					1234	apples	10
4	1234	apples	10
5	2345	pears	15			1234
6	3456	oranges	20
7	4567	bananas	25
Sheet1

Cell Formulas
Range	Formula
I3	I3	=INDEX(NamedRange,1,1)
J3	J3	=INDEX(NamedRange,1,2)
K3	K3	=INDEX(NamedRange,1,3)

Named Ranges
Name	Refers To	Cells
NamedRange	=Sheet1!$B$4:$D$4	I3:K3

Cells with Data Validation
Cell	Allow	Criteria
G5	List	=NamedRange