Repeating formula help

[Bound4Glory]

Hello,
I have about 100 pages on work sheet 1. At the top of each page I have 5 rows that I need extracted from a table on work sheet 2. There are 63 rows on each page. I basically need the formula to repeat every 63 rows and increase by 1 row from the table.

Anyone know how I can repeat the formula without having to copy and paste each one and then changing the numbers manually?

Work sheet 1 - Page 1

Date: Friday, September 3, 2021 Origin: PENNWOOD PLACE (PA) P&DC Route: 150AD Trip: 3003 STA: 15:00

Work sheet 1 - Page 2

Date: Friday, September 3, 2021 Origin: P60S CHICAGO STC Route: 602M5 Trip: 707 STA: 15:15

Work sheet 2 - Table

150AD 3003 09/03/2021 15:00:00 150PM PENNWOOD PLACE (PA) P&DC 602M5 707 09/03/2021 15:15:00 60S CHICAGO STC

Thank you for your time

 

[Toadstool]

Hi Bound4Glory,

Does this do what you want?

Bound4Glory.xlsx
	A	B	C
1	Code	Date/Time	Address
2	150AD 3003	9/3/2021 15:00	PENNWOOD PLACE (PA) P&DC
3	602M5 707	9/3/2021 15:15	P60S CHICAGO STC
4
Sheet2

Cell Formulas
Range	Formula
A2:A4	A2	=IF(ROW()-1>COUNTIF(Sheet1!A:A,"Origin:"),"",INDEX(Sheet1!C:C,((ROW()-2)*63)+3)&" "&INDEX(Sheet1!C:C,((ROW()-2)*63)+4))
B2:B4	B2	=IF(A2="","",TEXT(DATEVALUE(MID(INDEX(Sheet1!C:C,((ROW()-2)*63)+1),FIND(",",INDEX(Sheet1!C:C,((ROW()-2)*63)+1))+2,99)),"m/d/yyyy")&" "&TEXT(INDEX(Sheet1!C:C,((ROW()-2)*63)+5),"hh:mm"))
C2:C4	C2	=IF(A2="","",INDEX(Sheet1!C:C,((ROW()-2)*63)+2))

 

[Bound4Glory]

I don't think I did I great job explaining it, but I think you're on the correct path for what I need. Here is a better example below. I'm just wondering if there is a quick method to do this or will I have to copy and paste the formulas all the way down?

SHEET1

SHEET2

Thank you

 

[Toadstool]

So you want the reverse of what I supplied?
Create Sheet1 from Sheet2?

 

[Bound4Glory]

Yes, I think you did it backwards and I am having a hard time figuring out the formula.

 

[Toadstool]

You're latest example is inconsistent. It appears you've changed from 63 rows per set to either 9 or 10 rows per set. This solution will give 9 rows per set.

So this creates Sheet1 from Sheet2.
It supports up to 999 entries in Sheet2 and you'll need to copy down the Sheet1 A and B formulae at least as far as Sheet2 entries multiplied by 9 (as I note you've switched from 63 rows to 9 rows).

Bound4Glory2.xlsx
	A	B	C
1	ROUTE TRIP	DATE TIME	ORIGIN
2	150AD 3003	9/3/2021 15:00	PENNWOOD PLACE
3	602M5 707	9/3/2021 15:15	P60S CHICAGO
4	640L 4601	9/3/2021 15:20	KANSAS City
5
Sheet2

Bound4Glory2.xlsx
	A	B
1	Date:	Friday, September 3, 2021
2	Origin:	PENNWOOD PLACE
3	Route:	150AD
4	Trip:	3003
5	STA	15:00
6
7
8
9
10	Date:	Friday, September 3, 2021
11	Origin:	P60S CHICAGO
12	Route:	602M5
13	Trip:	707
14	STA	15:15
15
16
17
18
19	Date:	Friday, September 3, 2021
20	Origin:	KANSAS City
21	Route:	640L
22	Trip:	4601
23	STA	15:20
24
25
Sheet1

Cell Formulas
Range	Formula
A1:A25	A1	=IF(ROW()>(9*COUNTA(Sheet2!$A$2:$A$999)),"",CHOOSE(MIN(6,MOD(ROW()-1,9)+1),"Date:","Origin:","Route:","Trip:","STA",""))
B1:B25	B1	=IF(ROW()>(9*COUNTA(Sheet2!$A$2:$A$999)),"",CHOOSE(MIN(6,MOD(ROW()-1,9)+1),TEXT(INDEX(Sheet2!$B$2:$B$999,INT((ROW()-1)/9)+1)+0,"dddd, mmmm d, yyyy"),INDEX(Sheet2!$C$2:$C$999,INT((ROW()-1)/9)+1),LEFT(INDEX(Sheet2!$A$2:$A$999,INT((ROW()-1)/9)+1),FIND(" ",INDEX(Sheet2!$A$2:$A$999,INT((ROW()-1)/9)+1))-1),MID(INDEX(Sheet2!$A$2:$A$999,INT((ROW()-1)/9)+1),FIND(" ",INDEX(Sheet2!$A$2:$A$999,INT((ROW()-1)/9)+1))+1,99),TEXT(INDEX(Sheet2!$B$2:$B$999,INT((ROW()-1)/9)+1)+0,"hh:mm"),""))