Drag-fill results columns on one tab, when there are gaps between columns of source data on other tab

[Deed2]

Hi all,

First time poster and not particularly advanced with excel, so hope I am doing this correctly and the question makes sense.

I have two tabs of data:
1. Source data. I need to paste in new tables of data to this tab each quarter, and perform calculations on the new data to get key results.
2. Results data. I want a tab that brings across just the key results for each quarter, such that once I have pasted a new table in to tab 1 and the calcs are performed, I can just drag across the formula from the previous column on tab 2 to update.

Sounds simple but I cannot arrange it such that I can use either an index or vlookup and drag the results across to auto-update the new period results. This is largely due to not working out a way to arrange the data on tab 1 without having gaps between the columns of key results.

Wondering if someone has any ideas - would be very much appreciated. Hopefully the above makes sense with the images I have uploaded below?

Thanks a lot in advance for any attempt to help or to steer me towards where I can get help, if the outcome I'm after is achievable with this data.

 

[Peter_SSs]

Welcome to the MrExcel board!

Likely one reason that you have not had a quicker response is that helpers cannot easily copy from an image to test ideas. For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if something like this is any use to you.

Deed2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Jan-March 2022						Apr-June 2022						Jul-September 2022
2	Item 1	1					Item 1	10					Item 1
3	Item 2	2					Item 2	12					Item 2
4	Item 3	3					Item 3	14					Item 3
5	Item 4	4					Item 4	16					Item 4
6	Item 5	5					Item 5	18					Item 5
7	Item 6	6					Item 6	20					Item 6
8	Item 7	7					Item 7	22					Item 7
9	Item 8	8					Item 8	24					Item 8
10	Item 9	9					Item 9	26					Item 9
Sheet1

Deed2.xlsm
	A	B	C	D
1		Jan-March 2022	Apr-June 2022	Jul-September 2022
2	Item 1	1	10
3	Item 2	2	12
4	Item 3	3	14
5	Item 4	4	16
6	Item 5	5	18
7	Item 6	6	20
8	Item 7	7	22
9	Item 8	8	24
10	Item 9	9	26
Sheet2

Cell Formulas
Range	Formula
B2:D10	B2	=LET(v,INDEX(Sheet1!2:2,MATCH(B$1,Sheet1!$1:$1,0)),IF(v="","",v))

 

[Deed2]

Welcome to the MrExcel board!

Likely one reason that you have not had a quicker response is that helpers cannot easily copy from an image to test ideas. For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if something like this is any use to you.

Deed2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Jan-March 2022						Apr-June 2022						Jul-September 2022
2	Item 1	1					Item 1	10					Item 1
3	Item 2	2					Item 2	12					Item 2
4	Item 3	3					Item 3	14					Item 3
5	Item 4	4					Item 4	16					Item 4
6	Item 5	5					Item 5	18					Item 5
7	Item 6	6					Item 6	20					Item 6
8	Item 7	7					Item 7	22					Item 7
9	Item 8	8					Item 8	24					Item 8
10	Item 9	9					Item 9	26					Item 9
Sheet1

Deed2.xlsm
	A	B	C	D
1		Jan-March 2022	Apr-June 2022	Jul-September 2022
2	Item 1	1	10
3	Item 2	2	12
4	Item 3	3	14
5	Item 4	4	16
6	Item 5	5	18
7	Item 6	6	20
8	Item 7	7	22
9	Item 8	8	24
10	Item 9	9	26
Sheet2

Cell Formulas
Range	Formula
B2:D10	B2	=LET(v,INDEX(Sheet1!2:2,MATCH(B$1,Sheet1!$1:$1,0)),IF(v="","",v))

Thank you very much for this Peter - really appreciate you taking the time to respond.

I think your suggestion will help with a 'next step' issue with my data, but doesn't quite get me over the line for my current issue - this is more to do with my question being a bit incoherent than anything else. Need to do something a bit less optimal to deal with my issue in the timeframe, but will come back to it.

Yep I signed up and posted my question pretty quickly to deal with something I needed fast, and have a bit to do to familiarise myself with using the forum the best / most appropriate way. Will check out XL2BB as suggested - suspect I am going to plead help from the forum more than once.

Thank you again - I realise my question and way of asking it make it hard work to understand what I want, so really appreciate you making the effort to look at it.
Dave

 

[Peter_SSs]

You're welcome. If you do need anything further, don't hesitate to post back with details.