copy every nth line and column to a new sheet

[stwigge]

Hi there,
I have researched this, and found answers, but unfortunately can't adapt the formula to my specific needs.
I think I either need an OFFSET formula or an INDEX formula.

Basically, I need to make a summary on Sheet 2, of what I have on Sheet1.
To do this, I need to copy the formula of certain lines, and of course it would take me forever, if i had to do every formula line by line.

I need to show it you by attachment - how do i upload an attachment, because i don't have rights at the min. Is this becos I just registered today?!!!

Many thanks
stwigge

 

[p45cal]

Excel Workbook
	A	B	C	D
1	Date	Drop off	Pick up	Total
2	Wednesday 30 11 11	7	19	26
3	Thursday 01 12 11	0	0	0
4	Friday 02 12 11	0	0	0
5	Saturday 03 12 11	0	0	0
6	Sunday 04 12 11	0	0	0
7	Monday 05 12 11	0	0	0
Sheet

BUT, this requires a small change on the Vehicle Summary sheet, where ALL the cells in column A of the table should contain a date, simple enough, the existing formula can be copied down. The table can be made to look as before by having the font colour and background colour the same in those cells where you want the date invisible. In this file, I've made the extra dates faint grey. Again, this is easy to do by formatting 1 week as you like, then copying down by dragging that block of 1 week.

 

[stwigge]

Re: arrays working slowly

perfect. that sumif formula works a treat, thank you.
that's all i needed...


just back to an earlier question i had about 'array formulas'..

i wasn't keen on them, in that they tend to, from my experience, be a little slow.

whenever i have this file open, this file, and any other excel file I have open, works really really slowly - i.e. bottom left, the message 'calculating cells' displays, counting slowly from 1-100%.
Even if there is a simple formula working, it will go slowly, if the workbook with the arrays is open. If i close the workbook with the arrays, then everything will work normally again.

I have tried this on various computers now, so it doesn't seem to be just a specific computer being slow.

Is there any way to get around this, in not having arrays? Or do you have more experience you wish to share on this?
Because at the minute, its not really workable, because whilst that workbook is open, other workbooks that i'm using, are giving incorrect answers to cells, due to the timetaking to update the correct values after 'calculating cells'

Many many thanks

 

[p45cal]

Re: arrays working slowly

Excel Workbook
	A	B	C	D	E
1	Date	Vehicle	Driver	Drop off	Pick up
2	Wednesday 30 11 11	VW Transporter	Driver 1	1	5
3	Wednesday 30 11 11	Renault Traffic	Driver 2	2	6
4	Wednesday 30 11 11	VW Sharan	Driver 3	0	0
5	Wednesday 30 11 11	Spare	Driver 4	4	8
6	Thursday 01 12 11	VW Transporter	Driver 1	0	0
7	Thursday 01 12 11	Renault Traffic	Driver 2	0	0
8	Thursday 01 12 11	VW Sharan	Driver 3	0	0
9	Thursday 01 12 11	Spare	Driver 4	0	0
Sheet

A2:A5 all the same, then A6 copied down.


Then these below, which can be copied down in blocks of 4 rows. Do this for the first 8 days, format as desired then select days 2 to 8 and drag down as far as you want.
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B2</td><td>=INDEX('Transfer Timetable 11-12'!C:C,MATCH(A2,'Transfer Timetable 11-12'!B:B,0)+1)</td></tr><tr><td>C2</td><td>=INDEX('Transfer Timetable 11-12'!C:C,MATCH(A2,'Transfer Timetable 11-12'!B:B,0)+4)</td></tr><tr><td>D2</td><td>=INDEX('Transfer Timetable 11-12'!$M:$M,MATCH($A2,'Transfer Timetable 11-12'!$B:$B,0))</td></tr><tr><td>E2</td><td>=INDEX('Transfer Timetable 11-12'!$W:$W,MATCH($A2,'Transfer Timetable 11-12'!$B:$B,0))</td></tr><tr><td>B3</td><td>=INDEX('Transfer Timetable 11-12'!C:C,MATCH(A3,'Transfer Timetable 11-12'!B:B,0)+6)</td></tr><tr><td>C3</td><td>=INDEX('Transfer Timetable 11-12'!C:C,MATCH(A3,'Transfer Timetable 11-12'!B:B,0)+9)</td></tr><tr><td>D3</td><td>=INDEX('Transfer Timetable 11-12'!$M:$M,MATCH($A3,'Transfer Timetable 11-12'!$B:$B,0)+5)</td></tr><tr><td>E3</td><td>=INDEX('Transfer Timetable 11-12'!$W:$W,MATCH($A3,'Transfer Timetable 11-12'!$B:$B,0)+5)</td></tr><tr><td>B4</td><td>=INDEX('Transfer Timetable 11-12'!C:C,MATCH(A4,'Transfer Timetable 11-12'!B:B,0)+11)</td></tr><tr><td>C4</td><td>=INDEX('Transfer Timetable 11-12'!C:C,MATCH(A4,'Transfer Timetable 11-12'!B:B,0)+14)</td></tr><tr><td>D4</td><td>=INDEX('Transfer Timetable 11-12'!$M:$M,MATCH($A4,'Transfer Timetable 11-12'!$B:$B,0)+10)</td></tr><tr><td>E4</td><td>=INDEX('Transfer Timetable 11-12'!$W:$W,MATCH($A4,'Transfer Timetable 11-12'!$B:$B,0)+10)</td></tr><tr><td>B5</td><td>=INDEX('Transfer Timetable 11-12'!C:C,MATCH(A5,'Transfer Timetable 11-12'!B:B,0)+16)</td></tr><tr><td>C5</td><td>=INDEX('Transfer Timetable 11-12'!C:C,MATCH(A5,'Transfer Timetable 11-12'!B:B,0)+19)</td></tr><tr><td>D5</td><td>=INDEX('Transfer Timetable 11-12'!$M:$M,MATCH($A5,'Transfer Timetable 11-12'!$B:$B,0)+15)</td></tr><tr><td>E5</td><td>=INDEX('Transfer Timetable 11-12'!$W:$W,MATCH($A5,'Transfer Timetable 11-12'!$B:$B,0)+15)</td></tr></table></td></tr></table>

Apply conditonal formatting to the whole range while A2 is the active cell:
<table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td>Conditional formatting </td></tr><tr><td><table style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"><tr><td>Cell</td><td>Nr.: / Condition</td><td>Format</td></tr><tr><td>A2:E??</td><td>1. / Formula is =OR($E2>0,$D2>0)</td><td style="background-color:#ff0000; ">Abc</td></tr></table></td></tr></table>

 

[stwigge]

thats excellent thank you.
its usable now without the arrays!
thanks