Fill

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DAP - 19 Aug 2008 06:33 GMT

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!

Reply to this Message

Max - 19 Aug 2008 06:42 GMT

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)]

Signature

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!

Reply to this Message

DAP - 19 Aug 2008 19:16 GMT

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:
[quoted text clipped - 16 lines]
> > click on the fill button and type in 6 then OK... it still increments by 1.
> > Help!

Reply to this Message

David Biddulph - 19 Aug 2008 19:42 GMT

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
>
[quoted text clipped - 27 lines]
>> > by 1.
>> > Help!

Reply to this Message

Max - 20 Aug 2008 04:13 GMT

> 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.

Signature

Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---

Reply to this Message