Pulling Data from range given Date Criteria in Rows

[tomtom412]

Hi there,

I'm creating a dashboard type sheet in which I want to automatically pull in data from a raw data sheet, for a given week selected within the dashboard.

Basically I want to automatically populate the numbers from A9:E9, given that I've selected my "wk start" date at the top in B2. If the data was arranged vertically, VLOOKUP would be a way to populate this, however I need the data arranged horizontally, in the format shown in the image.

I've pasted below the two sheets in my workbook - 1 is the report sheet which pulls in the raw data that is populated by the user into Sheet 2 - Raw Data

Any help as to how to do this would be greatly appreciated as I'm unsure how to get what I want!

Thanks,
Tom

1. Report Sheet:

Sample Data Sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	wk start	15-Aug
3	wk end	19-Aug
4
5	Aug-22
6	15-Aug	16-Aug	17-Aug	18-Aug	19-Aug
7	M	T	W	T	F
8							each row fills in with its matching data for that week from the RAW DATA tab
9
10
11
12
13
14
15
16
17
18
19
Report

Cell Formulas
Range	Formula
B3	B3	=B2+4
A5	A5	=A6
A6	A6	=B2
B6:E6	B6	=A6+1

Cells with Data Validation
Cell	Allow	Criteria
B2	List	='RAW DATA'!$A$2:$AS$2

2. Raw Data Sheet:

Sample Data Sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1	Aug-2022					Aug-2022					Aug-2022					Sep-2022					Sep-2022					Sep-2022					Sep-2022					Oct-2022					Oct-2022
2	15	16	17	18	19	22	23	24	25	26	29	30	31	01	02	05	06	07	08	09	12	13	14	15	16	19	20	21	22	23	26	27	28	29	30	03	04	05	06	07	10	11	12	13	14
3	M	T	W	T	F	M	T	W	T	F	M	T	W	T	F	M	T	W	T	F	M	T	W	T	F	M	T	W	T	F	M	T	W	T	F	M	T	W	T	F	M	T	W	T	F
4	8	8	8	8	8	4	0	0	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	-	8	8	8	8	8	8	8	8	8
5	6	6	8	7	7	4	0	0	4	6	8	4	6	6	4	6	3	4	6	4	8	3	4	6	4	3	2	0	4	6	8	3	4	6	4	-	6	9	9	8	6	3	4	11	9
6	1	0	1	1	0	1	0	0	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	-	0	0	0	0	2	0	1	0	0
7
RAW DATA

Cell Formulas
Range	Formula
A1,F1,K1,P1,U1,Z1,AE1,AJ1,AO1	A1	=A2
A2	A2	=TODAY()
B2:E2,G2:J2,L2:O2,Q2:T2,V2:Y2,AA2:AD2,AF2:AI2,AK2:AN2,AP2:AS2	B2	=A2+1
F2,K2,P2,U2,Z2,AE2,AJ2,AO2	F2	=E2+3
A3:AS3	A3	=LEFT(TEXT(A2,"ddd"),1)

 

[Kerryx]

HLOOKUP ??

 

[Fluff]

What version of Excel areyou using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Kerryx]

Book1
	A	B	C	D	E
2	wk start	29/08/2022
3	wk end	02/09/2022
4
5	01/08/2022
6	29/08/2022	30/08/2022	31/08/2022	01/09/2022	02/09/2022
7	M	T	W	T	F
8	8	8	8	8	8
9	8	4	6	6	4
10	0	0	0	0	0
REPORT

Cell Formulas
Range	Formula
B3	B3	=B2+4
A6	A6	=B2
B6:E6	B6	=A6+1
A8:E8	A8	=HLOOKUP(A$6,'RAW DATA'!$A$2:$AS$6,3,FALSE)
A9:E9	A9	=HLOOKUP(A$6,'RAW DATA'!$A$2:$AS$6,4,FALSE)
A10:E10	A10	=HLOOKUP(A$6,'RAW DATA'!$A$2:$AS$6,5,FALSE)

 

[tomtom412]

What version of Excel areyou using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Understood and updated. Thank you for that!

 

[tomtom412]

HLOOKUP ??

Ah - feel like an idiot. Of course there is a horizontal lookup! Thank you for this and your help, greatly appreciated!

 

[Kerryx]

Glad to help