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]

I jus

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))), "")

I just noticed one typo in the formula, it will not make any errors in the output with your request as it is, But, for cleanness sake it should be fixed. In the 3rd "CHOOSE" function the 24 should be replaced by a double quote (Empty space) ... "".

The correct formula in cell K4 should be:

=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,""),
                                 CHOOSE((1+MOD(COLUMN(K4)-11,4)),7,"",6,4))),
 "")

 

[awoohaw]

I just saw another typo, again, it won't make a difference if you start everything in K4. but if you need to start higher or to the left it will.
The I4 should be K4.

Also, the range after INDEX( is where your data is ... so you need to change that accordingly as well.

 =
IFERROR(
    INDEX(
        $B$4:$H$8,
        (1+INT((ROW(K4)-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,""),
             CHOOSE((1+MOD(COLUMN(K4)-11,4)),7,"",6,4))),
"")

I suggest you start testing this in cell K4 if at all possible. then copy it around as you need.
Please remember if you need to start on a different ROW you need to change the subtraction to the number of whatever row you are on.
And if you start in a different column you need to change the subtraction to the number of whatever column you are on.

 

[Gazz292]

thankyou for this help.

i'm playing about with the formula and figuring out how it works right now.

Does it matter that my data table and the timetable are on seperate sheets in excel, does this make a difference to how your formulas work or is it just a matter of adjusting the cell references across sheets?

The other thing i'm wondering about, the station name and stop type,
this needs to be the 2 pieces of data Concatenated (i think that's the right word) as the station name can vary in length,
if it's just a station you drive through, then it's name has nothing added to the end,
but if it's one you stop at, the station name has ' ;ph' added to the end of it's name (or ' ;zd,ph', ' ;pt' and so on depending on the stop type)

 

[awoohaw]

The Source data being on different sheets makes no difference.
The first part of the index formula is the only place where the source data is mentioned. All of the other calculatons are based upon mathematics of the column number and row number that the formula is in.

So, if the data is on a sheet called "Data Sheet" and the Game Grids are a sheet called "Game Sheet" and using the formula above:

change the formula as follows. Sheet names with spaces in the name must be wrapped in apostrophes, and sheet names are followed by an exclamation mark then the cell references. I have only added the sheet name to the last formula I posted. And I have intentionally not wrapped in in HTML so you can see what I have emboldened:

=IFERROR(
INDEX(
'Data Sheet'!$B$4:$H$8,
(1+INT((ROW(K4)-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,""),
CHOOSE((1+MOD(COLUMN(K4)-11,4)),7,"",6,4))),
"")


Your second question may be very hard to accommodate. Was this in your original images? If so, I missed it. But, let me ask for clarification.... and additional information.
1. Is the concatenation the combining of (for reference purposes) cells 1. A1 and A2 and the additional text, or 2. just A2 and the additional text?
2, What determines the additional text, is there another data element somewhere? Are there circumstances when there is a Stop and no additional text need to be concatenated?
3. How is the concatenation punctuated? Is there a space after the stop? Any punctuation marks?
4. It is probably a good idea to tell the forum what the time schedules are used for, and how you want them integrated into the game grid as well.

 

[Gazz292]

I've managed to get the XL2BB thing working, and attach a 'mini sheet' below showing i have got the formula working, (it seems to format badly in the forum preview, all the rows after row 8 should be 2mm tall to show the timetable as i intend it to look, unfortunately this means i have to merge certain columns to get the required layout, i've use A4 as the sheet size here.

I'm still figuring out the maths that makes this work and how it changes the cells it uses for the calculations, but i changed the '(1+INT((ROW(K4)-4)/3)),' to '(1+INT((ROW(K4)-4)/6)),' due to the 6 rows i have per station entry, the 6 rows thing is needed mostly for the speed limit side of the timetable (the empty bits to the left of the station entries that i will tackle next, hopefully with the same formula you made for me, just reading it's data from a different table).

To answer the questions above..
4. They are drivers timetables for a train driving simulator (SimRail), they will be loaded onto an e-ink kindle in PDF format, so everything has to be sized to fit the kindle's screen, and show as individual pages you flip through like a book as your drive progresses, they are to look like a real Polish train drivers paper timetable.

Each entry on the right is a 'station or dispatcher post' area that you drive through, each station having different times you should drive past it to keep to the schedule, the entries with an arrival and departure time (also in bold and with ' ;pt' after the station name) mean stop here for passenger exchange, the 'mileage' being where you are along the line and so on.


1 - 3. i realised that i could do away with the need to combine the text from the 'station / post' and 'stop type' columns by simply combining those 2 columns in power query, the original data is a Json file from the train simulator, with many separate entries per 'station'
i've only just realised i can combine columns in power query to make things easier.

I haven't however figured out how to alter the formula for when i remove the 'stop' column yet, so that column is still present but empty in the 'layout testing' sheet which i am playing with the formula on, which have put below as a mini-sheet (i hope)


I now have a new 'problem' some timetable entries need to be made longer, i.e 9, 12 or 16 rows (with just blank space in the middle of them) This is to give enough room for loads of speed changes in a 'station area'
But if i add some blank rows in the middle of a station entry, this messes up the following station's data, i guess i need to play with some of the numbers in the formula when i need to make a station entry taller?

Once again, thankyou so much for helping me with this.

testing layout.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X		Z	AA
1
2
3	Station		Arrival	Depart	Radio	StatInfo	Mileage
4	Abc ;zd, ph		01:49	03:59	R1, R2,	RT, H, SS	6.020
5	Def		|	10:25	R3,	RT, H, PP	3.768
6	Ghi ;ph		20:21	20:31	E3	F3	3.143
7	Jkl		|	19:10	E4	F4	7.713
8	Mno ;pt		19:06	21:36	E5	F5	7.643
9																			Abc ;zd, ph			0.075694444	01:49
10
11																					R1, R2,
12
13																			6.020		RT, H, SS		03:59
14
15																			Def			|	|
16
17																					R3,
18
19																			3.768		RT, H, PP		10:25
20
21																			Ghi ;ph			0.847916667	20:21
22
23																					E3
24
25																			3.143		F3		20:31
26
27																			Jkl			|	|
28
29																					E4
30
31																			7.713		F4		19:10
32
33																			Mno ;pt			0.795833333	19:06
34
35																					E5
36
37																			7.643		F5		21:36
38
39
40
41
42
43
44
45
46
47
Sheet5

Cell Formulas
Range	Formula
S9,V39,S39,V33,S33,V27,S27,V21,S21,V15,S15,V9	S9	=IFERROR( INDEX($A$4:$G$8, (1+INT((ROW(H4)-4)/6)), CHOOSE((1+MOD((ROW(H4)-4),3)), CHOOSE((1+MOD(COLUMN(I4)-9,4)),1,2,"",3), CHOOSE((1+MOD(COLUMN(I4)-9,4)),"","",5,""), CHOOSE((1+MOD(COLUMN(I4)-9,4)),7,"",6,4))), "")
W9,W39,W33,W27,W21,W15	W9	=IFERROR( INDEX($A$4:$G$8, (1+INT((ROW(L4)-4)/6)), CHOOSE((1+MOD((ROW(L4)-4),3)), CHOOSE((1+MOD(COLUMN(M4)-10,4)),1,2,"",3), CHOOSE((1+MOD(COLUMN(M4)-10,4)),"","",5,""), CHOOSE((1+MOD(COLUMN(M4)-10,4)),7,"",6,4))), "")
T11,T41,T35,T29,T23,T17	T11	=IFERROR( INDEX($A$4:$G$8, (1+INT((ROW(L5)-4)/6)), CHOOSE((1+MOD((ROW(L5)-4),3)), CHOOSE((1+MOD(COLUMN(N5)-11,4)),1,2,"",3), CHOOSE((1+MOD(COLUMN(N5)-11,4)),"","",5,24), CHOOSE((1+MOD(COLUMN(N5)-11,4)),7,"",6,4))), "")
S43:U43,S37:U37,S31:U31,S25:U25,S19:U19,S13:U13	S13	=IFERROR( INDEX($A$4:$G$8, (1+INT((ROW(H9)-4)/6)), CHOOSE((1+MOD((ROW(H9)-4),3)), CHOOSE((1+MOD(COLUMN(I9)-9,4)),1,2,"",3), CHOOSE((1+MOD(COLUMN(I9)-9,4)),"","",5,""), CHOOSE((1+MOD(COLUMN(I9)-9,4)),7,"",6,4))), "")
U11,U41,U35,U29,U23,U17	U11	=IFERROR( INDEX($A$4:$G$8, (1+INT((ROW(J8)-4)/6)), CHOOSE((1+MOD((ROW(J8)-4),3)), CHOOSE((1+MOD(COLUMN(K8)-9,4)),1,2,"",3), CHOOSE((1+MOD(COLUMN(K8)-9,4)),"","",5,""), CHOOSE((1+MOD(COLUMN(K8)-9,4)),7,"",6,4))), "")
W13,W43,W37,W31,W25,W19	W13	=IFERROR( INDEX($A$4:$G$8, (1+INT((ROW(L6)-4)/6)), CHOOSE((1+MOD((ROW(L6)-4),3)), CHOOSE((1+MOD(COLUMN(M6)-10,4)),1,2,"",3), CHOOSE((1+MOD(COLUMN(M6)-10,4)),"","",5,""), CHOOSE((1+MOD(COLUMN(M6)-10,4)),7,"",6,4))), "")

 

[Gazz292]

Not sure that will display properly without the cell sizes,
this is a picture of what i looks like to me in excel:

 

[awoohaw]

well, I don't really know what to say. You have it figured out how to move down rows.
Regarding the formatting and display. I have learned to NEVER use merge cells, inconsistent row heights, formatting in nearly any situation.
It just compounds problems when you have to change something that was built before the goofy formatting.
Additionally, you already figured out that to have a complete section and no overlap you need to use the maximum number of possible rows and stick with it.
You can't have 6 rows on one side and 12 or 18 on the other. You have to use the max of any you need. So, in your case you can only repeat things every 18 rows.

The secret in the formula I gave you is that is uses the same formula in every cell. You'll need to figure out how to include the station stops schedule with in that.

But, one suggestion is to wrap to the next column, So, if a schedule has 14 stops, you should have 3 columns of 6,6,2. That way you keep the row length the same. There are two functions in 365 that may help you with that... WRAPROWS and WRAPCOLUMNS (i think those are the names). I have not used them before but it may help with that station time line stops schedule to the side.

 

[Gazz292]

Unfortunately i don't think i can get the layout i need for the timetable without merging cells, part of the need for 6 x 2mm rows is the speed side of the timetable has this thing where the 'mileage' entry is half above the speed entry, yet can also be level with it when the line changes.

I think i'm pushing what excel is designed for with this kind of layout, but programming and formulas (even maths) is hard for me, so i have no idea of another way to get things to 'line up' as in a real timetable.

Below is a picture of one page of a completed timetable i made by manually assigning all the cell references, it took forever to do this and was prone to errors as i assigned the cells, thankfully producing the 24 timetables for the 1 hour service through the day was easy with a button to update the times before saving them as pdf's.

 

[awoohaw]

Okay. Best wishes. I enjoyed the exercise.