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

 

[VoG]

It isn't possible for anybody to attach a workbook on this forum.

You can use an HTML maker like Excel Jeanie to post a shot of your sheet http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[p45cal]

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?!!!

No it's not, attachments don't work here, use the interweb, say box.net.
You may have difficulties pasting links too until you've posted a number of times, if so spell any link out like:
www dot box dot net forwardslashv ssdderdds dot whatever

 

[stwigge]

ok thanks. well iv shared it on this folder link. hopefully the file can be seen here

https://ultimatesnowsports.livedrive.com/Item/23888062

 

[p45cal]

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.

OK, got the file, now you're not being very certain with your 'certain lines', so more details required now.

…and does it have to be formulae you copy rather than values?

 

[stwigge]

hi there.
sorry. i tried to show it on the second sheet.
for the first four lines, i have showed the formula i would like.
which i wud like to be repeated for every day

i hope that makes sense now

 

[p45cal]

The formulae are all ARRAY-ENTERED (see note at bottom of diagram).
Flaky, because it depends on the row number the formula is in, and on finding instances of "Vehicle Name 1" in column B as those instances are the base offsets from which all the other values are obtained for that day.
You can copy down the sheet, but should do so by selecting A2:D5 (shaded below) and dragging down.:

Excel Workbook
	A	B	C	D
1	Date	Vehicle	Drop off	Pick up
2	Wednesday 30 11 11	VW Transporter	1	11
3		Renault Traffic	2	12
4		VW Sharan	3	13
5		VW Transporter	4	14
6	Thursday 01 12 11	VW Transporter	5	15
7		Renault Traffic	6	16
8		VW Sharan	7	17
9		VW Transporter	8	18
10	Friday 02 12 11	VW Transporter	9	19
11		Renault Traffic	10	20
12		VW Sharan	0	0
13		VW Transporter	0	0
14	Saturday 03 12 11	VW Transporter	0	0
15		Renault Traffic	0	0
16		VW Sharan	0	0
17		VW Transporter	0	0
18	Sunday 04 12 11	VW Transporter	0	0
19		Renault Traffic	0	0
20		VW Sharan	0	0
21		VW Transporter	0	0
22	Monday 05 12 11	VW Transporter	0	0
23		Renault Traffic	0	0
24		VW Sharan	0	0
25		VW Transporter	0	0
Summary

 

[stwigge]

ok excellent thank you.
that seems to work.
was hoping not to have an array formula, but if it works than great.
thought that there was an option for some sort of offset formula.
when you say flaky because of the 'vehicle name'. is it open for mistakes for me to have 2 vehicles called exactly the same name? i can get around this if required. but its just that the same vehicle can be used on the same day
many thanks again

 

[p45cal]

ok excellent thank you.
that seems to work.
was hoping not to have an array formula, but if it works than great.
thought that there was an option for some sort of offset formula.

They are offset formulae, based on finding that single instance of the literal string Vehicle Name 1, if that's not there it will fail.

when you say flaky because of the 'vehicle name'. is it open for mistakes for me to have 2 vehicles called exactly the same name?

Yes, you can, and do, have vehicles with the same name. See above

i can get around this if required. but its just that the same vehicle can be used on the same day

no need.

It's important that the results table formulae start on row 2 and that the source table starts where it does at the moment. Change either of those and it will screw up (because opf refs. to row()). Flaky.

 

[stwigge]

excellent. seems to work fine.

here is the ammended sheet
https://ultimatesnowsports.livedrive.com/Item/23888062

one last thing - i would like to summarise these results on the 3rd sheet

unsure of the formula i would need for the result in B2 and C2 of the sheet 'Daily Summary'
(which would be the sum of 'Vehicle Summary' sheet of D2:D5 and E2:E5 respectively)

many thanks