Fill

[DAP]

I am trying to fill in cells in one sheet with every 6th cell from another
sheet. I cannot get the fill feature to increment every 6th cell on the
reference sheet. Help! When I drag the handle it increments by one. When I
click on the fill button and type in 6 then OK... it still increments by 1.
Help!

 

[Max]

In Sheet1,
you can place this in any starting cell, say in B2:
=OFFSET(Sheet2!$A$1,ROWS($1:1)*6-6,)
Then just copy B2 down as far as required

This returns in B2 down:
=Sheet2!A1
=Sheet2!A7
=Sheet2!A13
and so on

Adapt accordingly to suit:
Sheet2!$A$1 : the anchor, ie the starting source cell in Sheet2
ROWS($1:1)*6-6 : the step "6"
[Do not change the core incrementer term: ROWS($1:1)]
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik

 

[DAP]

You are a Stud ! Worked great

It works but I dont really understand why?

Please explain the offset command and the command Rows ($1:1)*6-6 ?

Thanks

In Sheet1,
you can place this in any starting cell, say in B2:
=OFFSET(Sheet2!$A$1,ROWS($1:1)*6-6,)
Then just copy B2 down as far as required

This returns in B2 down:
=Sheet2!A1
=Sheet2!A7
=Sheet2!A13
and so on

Adapt accordingly to suit:
Sheet2!$A$1 : the anchor, ie the starting source cell in Sheet2
ROWS($1:1)*6-6 : the step "6"
[Do not change the core incrementer term: ROWS($1:1)]
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---

I am trying to fill in cells in one sheet with every 6th cell from another
sheet. I cannot get the fill feature to increment every 6th cell on the
reference sheet. Help! When I drag the handle it increments by one. When I
click on the fill button and type in 6 then OK... it still increments by 1.
Help!

 

[David Biddulph]

OFFSET and ROWS are standard Excel functions. Their syntax is explained if
you type the function name into Excel help. [Excel help will similarly help
you with any other Excel function (except one).]
--
David Biddulph

You are a Stud ! Worked great

It works but I dont really understand why?

Please explain the offset command and the command Rows ($1:1)*6-6 ?

Thanks

In Sheet1,
you can place this in any starting cell, say in B2:
=OFFSET(Sheet2!$A$1,ROWS($1:1)*6-6,)
Then just copy B2 down as far as required

This returns in B2 down:
=Sheet2!A1
=Sheet2!A7
=Sheet2!A13
and so on

Adapt accordingly to suit:
Sheet2!$A$1 : the anchor, ie the starting source cell in Sheet2
ROWS($1:1)*6-6 : the step "6"
[Do not change the core incrementer term: ROWS($1:1)]
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---

I am trying to fill in cells in one sheet with every 6th cell from
another
sheet. I cannot get the fill feature to increment every 6th cell on the
reference sheet. Help! When I drag the handle it increments by one.
When I
click on the fill button and type in 6 then OK... it still increments
by 1.
Help!

 

[Max]

You are a Stud ! Worked great

That's good. But could you press the "Yes" button in that response since it
answered your original query

It works but I dont really understand why?
Please explain the offset command and the command Rows ($1:1)*6-6 ?

This is a second query. Just a couple of add-ons, assuming you have since
updated your knowledge in Excel's help as per David's comment

ROWS($1:1)*6-6 is the row param in OFFSET

To see what the above does (you could this in general for any nested
functions), just put in any cell: =ROWS($1:1)*6-6, then copy it down. You
would find it simply generates the number series: 0, 6, 12, ... . These
numbers (incrementing as desired) are then used as the row param in OFFSET to
return the required results based on the OFFSET's anchor cell.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---