Advance cell reference only one column when formula is pasted 7 columns away

[megera716]

This is probably a piece of cake for you gurus but I just can't figure it out. I've got a list of locations I'm referencing on another sheet and multiple column headings under each location.

When I copy the formula in C3 for Location 1 to J3 for Location 2, I want the cell reference to move just one column to ='Prior Period Rptng'!O5, not move 7 columns to Location 8 in U5.

What I'm getting:

What I want:

 

[Jeffrey Mahoney]

My first attempt. See if this works

Book2
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Location 1	Location 1	Location 1	Location 1	Location 1	Location 1	Location 1	Location 2	Location 2	Location 2	Location 2	Location 2	Location 2	Location 2
4	Location 1							Location 2
5	Q3 2024	2024 Budget	Q3 2024 Variance Budget	Q2 '24	Q3 vs Q2	Q3 '23	Q3 '24 vs Q3 '23	Q3 2024	2024 Budget	Q3 2024 Variance Budget	Q2 '24	Q3 vs Q2	Q3 '23	Q3 '24 vs Q3 '23
Sheet1

Cell Formulas
Range	Formula
C3:P3	C3	=INDEX($U$5:$AH$5,,ROUNDUP(COUNTA($C$5:C$5)/7,0))
C4,J4	C4	=C3

Book2
	U	V	W	X	Y	Z	AA
5	Location 1	Location 2	Location 3	Location 4	Location 5	Location 6	Location 7
Sheet1

 

[megera716]

That did it! Amazing! Thank you!

My first attempt. See if this works

Book2
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Location 1	Location 1	Location 1	Location 1	Location 1	Location 1	Location 1	Location 2	Location 2	Location 2	Location 2	Location 2	Location 2	Location 2
4	Location 1							Location 2
5	Q3 2024	2024 Budget	Q3 2024 Variance Budget	Q2 '24	Q3 vs Q2	Q3 '23	Q3 '24 vs Q3 '23	Q3 2024	2024 Budget	Q3 2024 Variance Budget	Q2 '24	Q3 vs Q2	Q3 '23	Q3 '24 vs Q3 '23
Sheet1

Cell Formulas
Range	Formula
C3:P3	C3	=INDEX($U$5:$AH$5,,ROUNDUP(COUNTA($C$5:C$5)/7,0))
C4,J4	C4	=C3

Book2
	U	V	W	X	Y	Z	AA
5	Location 1	Location 2	Location 3	Location 4	Location 5	Location 6	Location 7
Sheet1

 

[Peter_SSs]

For the layout in post 2, another option would be

=INDEX($U5:$AH5,(COLUMNS($C:C)+6)/7)