How to Pick a value from multiple sheets into Common Sheet Using a Commin Linking Field?

[gnaga]

Hi,

I have a data set in individual 12 sheets for each month which is manually entered by the users. I have a common sheet in which I would like to capture the entered data in the individual sheet into this common sheet. I need this in dynamic form as my row of data in individual sheet get interchanged from month to month. The example of data is posted here. The date in common sheet will continue in the same row, I mean after 31st of Jan it will start with 1st Feb and continue and so on. But individual sheet start from 1st day of month and end at last day of month. This specific day data for each ID, each month to be exactly captured in the common sheet on the same day and same month. My individual sheets are named like Jan-21, Feb-21...Dec-21.

GC-2 Annual Leave Plan 2021_Rev_0.xlsm
	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
2														Individaul Sheet
3			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
4			R	R	R	R	M	M	N	N	R	R	R	R	M	M	N	N	R	R	R	R	M	M	N	N	R	R	R	R	M	M	N
5	Name	ID
6	ABC	10	M				M	L	N	N	N				M	M	N	N	N	L	L	L	L	L	L	L	L	L	M	M	M	M	N
7	XYZ	20	N				M	M	N	N					M	M	L	N					M	M	N	N				M	M	M	N
8	PQR	30		M			M	M	N	N					M	M	N	N					L	M	N	N			M		M	M	N
9	TUV	40			M		M	M	N	N					M	M	N	N					M	M	N	N	N				M	M	L
10
11
12														Common Sheet
13			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
14			R	R	R	R	M	M	N	N	R	R	R	R	M	M	N	N	R	R	R	R	M	M	N	N	R	R	R	R	M	M	N
15	Name	ID
16	ABC	10	M				M	L	N	N	N				M	M	N	N	N	L	L	L	L	L	L	L	L	L	M	M	M	M	N
17	TUV	40			M		M	M	N	N					M	M	N	N					M	M	N	N	N				M	M	L
18	PQR	30		M			M	M	N	N					M	M	N	N					L	M	N	N			M		M	M	N
19	XYZ	20	N				M	M	N	N					M	M	L	N					M	M	N	N				M	M	M	N
Sheet1

Cell Formulas
Range	Formula
H13:AK13,H3:AK3	H3	=G3+1

 

[gnaga]

I am trying to make it simple. The following data explains bit simple I believe. I need a dynamic formula in common sheet to capture the data from individual sheet where the intersect of ID, Month and Day of Common sheet with Individual sheet. Hope bit clear.

Book1
	G	H	I	J	K	L	M	N	O	P	Q	R
1			Sheet-1
2			A	A	A	A	A
3	Name	ID	1	2	3	4	5
4	ABC	10	R	R	R	R	M
5	XYZ	20	M	M	N	N	R
6	PQR	30	R	R	M	M	N
7	TUV	40	N	N	R	R	R
8
9			Sheet-2
10			B	B	B	B	B
11	Name	ID	1	2	3	4	5
12	ABC	10	M	N	N	R	R
13	XYZ	20	R	R	R	M	M
14	TUV	40	R	M	M	N	N
15	PQR	30	N	R	R	R	R
16
17			Common Sheet
18			A	A	A	A	A	B	B	B	B	B
19	Name	ID	1	2	3	4	5	1	2	3	4	5
20	ABC	10	R	R	R	R	M	M	N	N	R	R
21	XYZ	20	M	M	N	N	R	R	R	R	M	M
22	PQR	30	R	R	M	M	N	N	R	R	R	R
23	TUV	40	N	N	R	R	R	R	M	M	N	N
Sheet1

 

[gnaga]

Hello Excel Experts and MVPs, can you guys give some clue/idea on this?

 

[gnaga]

Hi, Any idea from experts?????