I am stumped on a linking problem...

Is there a way to write a command that will transfer the value of every "6th"
cell of a column in worksheet "A" to a contiguous column of cells in
worksheet "B"? For example the value in "A!B6" is transferred to "B!A1",
"A!B12 to B!A2", "A!B18 to B!A3" etc. etc. I know I can manually reference
each target cell in Worksheet A to transfer over to Worksheet B, but the
scope of this project is such that I would like to write a function/command
that can then be copied and pasted. A normal copy and paste is dynamic one
row at a time and I need it to reference every 6th row.

Sorry for the "less than eloquent" description of my problem.

This formula works assuming your data starts in A!A1 and you want to
return every sixth cell starting in B!B1:
=OFFSET(A!$A$1,ROW(B1)*6-1,0)

This uses A!A1 as the starting point, and multiplies the row number of
this formula by 6 and subtracts 1 for the row offset.

In A1 of sheet B enter =INDIRECT("A!B"&ROW(A1)*6)
Copy this down the column
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"FishHead" <FishHead@discussions.microsoft.com> wrote in message
news:CF93203A-E01A-43D7-8AD3-646CA7A07AE1@microsoft.com...
> Is there a way to write a command that will transfer the value of every
> "6th"
> cell of a column in worksheet "A" to a contiguous column of cells in
> worksheet "B"? For example the value in "A!B6" is transferred to "B!A1",
> "A!B12 to B!A2", "A!B18 to B!A3" etc. etc. I know I can manually
> reference
> each target cell in Worksheet A to transfer over to Worksheet B, but the
> scope of this project is such that I would like to write a
> function/command
> that can then be copied and pasted. A normal copy and paste is dynamic
> one
> row at a time and I need it to reference every 6th row.
>
> Sorry for the "less than eloquent" description of my problem.
>

Thank you, thank you. Worked perfectly!

"Bernard Liengme" wrote:

> In A1 of sheet B enter =INDIRECT("A!B"&ROW(A1)*6)
> Copy this down the column
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "FishHead" <FishHead@discussions.microsoft.com> wrote in message
> news:CF93203A-E01A-43D7-8AD3-646CA7A07AE1@microsoft.com...
> > Is there a way to write a command that will transfer the value of every
> > "6th"
> > cell of a column in worksheet "A" to a contiguous column of cells in
> > worksheet "B"? For example the value in "A!B6" is transferred to "B!A1",
> > "A!B12 to B!A2", "A!B18 to B!A3" etc. etc. I know I can manually
> > reference
> > each target cell in Worksheet A to transfer over to Worksheet B, but the
> > scope of this project is such that I would like to write a
> > function/command
> > that can then be copied and pasted. A normal copy and paste is dynamic
> > one
> > row at a time and I need it to reference every 6th row.
> >
> > Sorry for the "less than eloquent" description of my problem.
> >
>
>
>