make template that reads data from table, moving down 1 row at a time, but the data is placed on 6 rows.

[Gazz292]

I'm trying to figure out a way to make a sort of template that i can insert into a sheet many times, to make a timetable for a game i'm playing, and each time i insert this 'template' it copies data from the next row down from the table,

all the data is in 2 tables, one for 'station data' the other for 'speed data', each row of the table has all the data needed for a single entry in the 'human readable sheet' but the 'template' thing needs to have at least 6 rows,
So at the moment when i try to copy and paste the timetable entry once i've set up the cell references to the tables cells, excel pulls the data from the table 6 rows down instead of one, thus it displays the wrong entry.

Below is part of my data sheet.

And below is the sheet where i make the data into a timetable, the yellow highlighter is to distinguish the 2 separate entries:

There are 2 entries on the above sheet, eventually there will be upto 50.
Each entry is 6 rows tall, but that may change occasionally as some entries that need more room, but i'd do that after the timetable is populated.

For the first entry i manually set it to pull the data from row 2 of the table,

And on the second entry i have typed the names of the columns in the tables that data is supposed to come from, so the second entry should be pulling it's data from row 3 of the table, and the next entry would pull the data from row 4 of the table and so on.

Is it possibly to make a tepmlate type thing that i can simply insert as needed, tell it the table row to read from, and it gets the correct data from the columns?

 

[awoohaw]

would you be kind enough to help the forum help you and use the xl2bb add in (Link below) to post a mini workbook of the three items?
If that is not possible can you just paste as a table? Also your formatting in the 3rd item makes it really hard to understand what is being mapped where.

 

[Gazz292]

i've only just loaded the factory image to my laptop after someone hacked in and started emptying my bank account via google pay (which i never even set up, but apparently it will get the money from any payment method it finds, i.e. paypal etc)
So i'd rather not run external tools on excel right now.

I'll see if i can explain it better,

ignore the speed data part bit, that's from a separate table and i'll tackle that later,
I want to make a template which is a load of cells and borders merged a certain way to mimic a real Polish train driver's timetable, and i would like the cells in each new template i add populate themselves from the next row down of the table.

So here's part of the table with the station entries, it's one station entry per row (and goes on for 60 rows):

And the timetable part (which i save as PDF files when it's fully populated)
I've put 3 entries on the timetable below by copying and pasting the top one, the top one has cell references to the tables row 2, hence it's filled in the correct data for that entry,
The 2 entries below i've simply cleared the data and put text in the cells to show where on the table they should get their data from:

Each new 'template' entry needs to pull data from the relevant columns of the table, but only drop down to the next row once, not 6 or 7 times as it does now.

Below is what happens if i copy and paste the top entry and leave it as it's filled itself in... the 2nd entry should be pulling data from row 3 "Warszawa Grochów R5".
But it is actually pulling data from row 8, which is the "Warszawa Zach. R19" entry, this i believe is due to the timetable entry being made up of 6 rows each time (and the actual data starts at row 2, as row 1 is the table headers)

So i'm wondering if i can make some sort of template that when i add it to the timetable sheet i simply tell it the table row number, and it gets the correct data from that row. then it auto advances to the next row each time i add it below the last 'template'

This is so that i can make timetables for different lines simply by swapping out the data in the table, and re; adding the number of timetable entries as appropriate (some services have 60 entries, some have only 5)

 

[awoohaw]

i've only just loaded the factory image to my laptop after someone hacked in and started emptying my bank account via google pay (which i never even set up, but apparently it will get the money from any payment method it finds, i.e. paypal etc)
So i'd rather not run external tools on excel right now.

I'll see if i can explain it better,

ignore the speed data part bit, that's from a separate table and i'll tackle that later,
I want to make a template which is a load of cells and borders merged a certain way to mimic a real Polish train driver's timetable, and i would like the cells in each new template i add populate themselves from the next row down of the table.

So here's part of the table with the station entries, it's one station entry per row (and goes on for 60 rows):

View attachment 87791

And the timetable part (which i save as PDF files when it's fully populated)
I've put 3 entries on the timetable below by copying and pasting the top one, the top one has cell references to the tables row 2, hence it's filled in the correct data for that entry,
The 2 entries below i've simply cleared the data and put text in the cells to show where on the table they should get their data from:

View attachment 87790

Each new 'template' entry needs to pull data from the relevant columns of the table, but only drop down to the next row once, not 6 or 7 times as it does now.

Below is what happens if i copy and paste the top entry and leave it as it's filled itself in... the 2nd entry should be pulling data from row 3 "Warszawa Grochów R5".
But it is actually pulling data from row 8, which is the "Warszawa Zach. R19" entry, this i believe is due to the timetable entry being made up of 6 rows each time (and the actual data starts at row 2, as row 1 is the table headers)

View attachment 87792

So i'm wondering if i can make some sort of template that when i add it to the timetable sheet i simply tell it the table row number, and it gets the correct data from that row. then it auto advances to the next row each time i add it below the last 'template'

This is so that i can make timetables for different lines simply by swapping out the data in the table, and re; adding the number of timetable entries as appropriate (some services have 60 entries, some have only 5)

if you can't do the add in. then post your data as a table. You're asking the forum to do a LOT of work to recreate your scenario.

 

[awoohaw]

Okay. This was quite an exercise. But I think I have it figured out. The INT and MOD Calculations need to be adjusted for your starting cell in the first cell of the box. Then copy over and then down.

I strongly recommend no merging of cells. If you want no gridlines, just say no gridlines from your view tab.

mr excel questions 15.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1											Station	Stop		Arrival
2													Radio
3		Station	Stop	Arrival	Depart	Radio	StatInfo	Mileage			Mileage		StatInfo	Depart
4		Warsza Gro	;zd;ph	00:34:00	00:37:00	R2,R1,R6	R307,RT,H,SS	4.254			Warsza Gro	;zd;ph		0.023611
5													R2,R1,R6
6											4.254		R307,RT,H,SS	0.025694
7
Sheet16

Cell Formulas
Range	Formula
K4:N6	K4	=IFERROR(INDEX($B$4:$H$10,(1+INT((ROW(I4)-4)/3)),CHOOSE((1+MOD((ROW(I4)-4),3)), CHOOSE((1+MOD(COLUMN(K4)-11,4)),1,2,"",3), CHOOSE((1+MOD(COLUMN(K4)-11,4)),"","",5,24), CHOOSE((1+MOD(COLUMN(K4)-11,4)),7,"",6,4))),"")

 

[awoohaw]

The above starts in cell K4, so the INT and MOD formulas are trying to get the numerator to be zero. So, to get the Row to zero for cell K4 you minus 4, to get the column to zero for cell K4 you - 11.

Also, I did not bring the time formats over for column N. You can correct that.

 

[Gazz292]

i'm sorry, i only started using excel a month ago.

I've tried to make things as tables, showing basically what i need... some way of formatting where data is pulled from a table, it's not going into another table but a series of cells that have been merged and had borders added to some to make it all look like a real timetable, probably something excel isn't really made for.


So, the data table:

A	B	C	D	E	F	G
A2	B2	C2	D2	E2	F2	G2
A3	B3	C3	D3	E3	F3	G3
A4	B4	C4	D4	E4	F4	G4
A5	B5	C5	D5	E5	F5	G5

And the cells if they were a table... the output table?:

A1 & B1		F1
	E1
C1	D1	G1

And each time another 'output table' is added below the existing one, it need to pull the data from the next row on the data table, so here it would be from row 2, then row 3 and so on.
but at the moment it pulls the data from the 7th row down, as each 'output table' is 6 rows tall.

But the output has to look like this (gridlines shown to make the data entry bits stand out) (showing the table references for 3 entries... and then i'll need to figure out to left hand side which is data from another table, but a similar thing, each entry pulls data from the next row down, not 6+ rows down as i get when i copy and paste and let excel update the cell references)

 

[Gazz292]

just noticed the replies, will read them now, thankyou

 

[awoohaw]

OK. if you cannot get the formulas to work just tell me what cell has the first station name, and what cell you want the output to begin.

 

[awoohaw]

Here it is with your sample data:

mr excel questions 15.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
1										Station	Stop		Arrival
2												Radio
3	Station	Stop	Arrival	Depart	Radio	StatInfo	Mileage			Mileage		StatInfo	Depart
4	A	B	01:49	03:59	E	F	6.02			A	B		01:49:00
5	A2	B2	09:28	10:25	E2	F2	3.768					E
6	A3	B3	20:21	20:31	E3	F3	3.143			6.02		F	03:59:00
7	A4	B4	18:50	19:10	E4	F4	7.713			A2	B2		09:28:00
8	A5	B5	19:06	21:36	E5	F5	7.643					E2
9										3.768		F2	10:25:00
10										A3	B3		20:21:00
11												E3
12										3.143		F3	20:31:00
13										A4	B4		18:50:00
14												E4
15										7.713		F4	19:10:00
16										A5	B5		19:06:00
17												E5
18										7.643		F5	21:36:00
Sheet16

Cell Formulas
Range	Formula
K4:N18	K4	=IFERROR( INDEX($B$4:$H$8, (1+INT((ROW(I4)-4)/3)), CHOOSE((1+MOD((ROW(I4)-4),3)), CHOOSE((1+MOD(COLUMN(K4)-11,4)),1,2,"",3), CHOOSE((1+MOD(COLUMN(K4)-11,4)),"","",5,24), CHOOSE((1+MOD(COLUMN(K4)-11,4)),7,"",6,4))), "")