Named ranges and dashboard

[pholt33]

I am trying to create a dashboard and have set up a bunch of named ranges for the sources, eventually to add some dropdowns with each person's name (Bob, Dave, Maria....). Each person's data has its own column.

I know that I can reference the named range directly, such as =BobsData , but how can I have a dynamic formula that references the dropdown cell and some text. I tried =A1&"Data" which resulted in BobsData in the cell and =INDIRECT(A1&"Data") but that gave #REF. I also tried adding including the sheet name but that also errored.

The data itself is relatively small, month-end values for the past year, so only 12 data points for each named range.

 

[Jeffrey Mahoney]

Please try this

Book2
	B	C	D	E	F	G	H
1	Bob	Nancy	Sharon	Brad		Brad
2	1	10	25	100			100
3	2	11	26	102			102
4	3	12	27	104			104
5	4	13	28	106			106
Sheet3

Cell Formulas
Range	Formula
H2:H5	H2	=INDEX(B2:E5,,MATCH(G1,B1:E1,0))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G1	List	=$B$1:$E$1

 

[Jeffrey Mahoney]

Now that you have the column of numbers you can Sum or Filter or Count any part of it.

 

[pholt33]

Please try this

Book2
	B	C	D	E	F	G	H
1	Bob	Nancy	Sharon	Brad		Brad
2	1	10	25	100			100
3	2	11	26	102			102
4	3	12	27	104			104
5	4	13	28	106			106
Sheet3

Cell Formulas
Range	Formula
H2:H5	H2	=INDEX(B2:E5,,MATCH(G1,B1:E1,0))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G1	List	=$B$1:$E$1

That works to the extent that every header is unique, which is not always the case. That's why I was hoping to use the named ranges. Is that possible?

 

[Jeffrey Mahoney]

You can still use named ranges

Book45a 20240313.xlsm
	B	C	D	E	F	G	H	I	J	K	L
1	Bob	Nancy	Sharon	Brad		Sharon	Output			NRTable
2	1	10	25	100			25			Bob	BobData
3	2	11	26	102			26			Nancy	NancyData
4	3	12	27	104			27			Sharon	SharonData
5	4	13	28	106			28			Brad	BradData
Sheet3

Cell Formulas
Range	Formula
H2:H5	H2	=INDIRECT(VLOOKUP(G1,NRTable,2,FALSE))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
NRTable	=Sheet3!$K$2:$L$5	H2

Cells with Data Validation
Cell	Allow	Criteria
G1	List	=$B$1:$E$1

 

[Jeffrey Mahoney]

My last post doesn't show that I created named ranges for each of the people in columns B through E