Copying formula

[F5JASON]

Hi, This is my first post so hope i'm doing it ok!

I want to copy a formula where:

A1 = B1 (on different tab)
A2 = B5 (on different tab)
A3 = B10 (on different tab)

So as i copy the first cell (or 2) down i want the formula to look 5 rows down on the formula, I have put 4 blank lines inbetween the B1 and B5 and the copied the formula down, then hid the rows, but i have thousands of rows to do so am really hoping there will be a quicker, and easier way to do this!

[NBVC]

Are you sure you want B1, B5, B10?... the blanks between B1 and B5 is 3 rows whereas the blanks between B5 and B10 is five rows.

[F5JASON]

Sorry, I was just using that as an example - turns out my exaple was rubbish! the actual cells i want it to follow is b3,b8, b13, b18, b23 etc with a 4 row gap in between

[6StringJazzer]

Try this in A1 and copy down:

=INDIRECT("Sheet2!B"&(ROW()-1*5)+3))

[NBVC]

Try:

=INDEX(Sheet3!B:B,5*(ROWS(Sheet3!$B$3:B3)-1)+3)

where Sheet3 is the name of the sheet to extract from

Copied down.

[F5JASON]

I'm really sorry, i cant get these formulas to work, any other ideas or could you please explain how the formula is broken down, i have moved the spreadsheet around so my details i need are in column G starting from 8, then 13, 15 etc. on a tab called Volumes... here is what i have:

=INDEX(Volumes!G:G,5*(ROWS(Volumes!$G$8:G8)-1)+3)

when i drag it it shows this as the next cell

=INDEX(Volumes!G:G,5*(ROWS(Volumes!$G$8:G13)-1)+3)

the data in cell G8 of volumes is 29.06

but it is showing 0 in my formula result

[NBVC]

Change to:

=INDEX(Volumes!G:G,5*(ROWS(Volumes!$G$8:G8)-1)+8)

That 8 offsets the start to G8

The ROWS(Volumes!$G$8:G8) doesn't matter as much as it counts rows in the range and as long as the first one resolves to 1, then it will be okay.

[F5JASON]

Thanks so much that worked beautifully! however, I am using this for monday to friday then have a 2 row gap (on the first sheet) for weekend days. and on the volumes tab i have the 5 rows per day (which the formula sorts for me) but at the end of the week we have 3 row gap. how do i encorporate this so i can copy down past the first 5 days?

Thanks again for all of your help!!

[NBVC]

Can you post a sample sheet showing what you mean?

[F5JASON]

I have attached the two sheets that i am tring to work with.

Thanks

[NBVC]

Are the dates in column A of each sheet supposed to match? They don't seem to in your example, but am not sure if that is the reality. Otherwise it is not an easy task with a single formula and you may need helper column in Volumes sheet to determine which to extract to the Summary.

[F5JASON]

Column B on summary needs to pick up G in Volume so...

Summary Volumes
B3 = G8
B4 = G13
B5 = G18

Sorry the dates don't match, as there is confidential information on some of the spreadsheet i copied a section of the sheet out.

but i would like to be able to drag the formula down, which i can do until B7 on summary
but because of the 2 row gap on summary and the 3 row gap on volumes it wont drag down.

[NBVC]

I am trying to see if there is another way around it which would give the desired results.

I am suggesting that if the dates in Column A are supposed to match, then we can do a lookup of column A to match the dates, then, get the appropriate 2nd item in the group associated to the date. Is that feasible?

If not, we can add a helper column that "indexes" each 2nd row from each date group and excludes the non-date rows... then we can use the "index" column to extract associated items.

My original formula will not be able to simplly skip sections, and bounce 2 rows in between..