software

Is there a way to write a command that will transfer the value of every quot;6thquot;
cell of a column in worksheet quot;Aquot; to a contiguous column of cells in
worksheet quot;Bquot;? For example the value in quot;A!B6quot; is transferred to quot;B!A1quot;,
quot;A!B12 to B!A2quot;, quot;A!B18 to B!A3quot; 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 quot;less than eloquentquot; 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(quot;A!Bquot;amp;ROW(A1)*6)
Copy this down the column
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;FishHeadquot; gt; wrote in message
...
gt; Is there a way to write a command that will transfer the value of every
gt; quot;6thquot;
gt; cell of a column in worksheet quot;Aquot; to a contiguous column of cells in
gt; worksheet quot;Bquot;? For example the value in quot;A!B6quot; is transferred to quot;B!A1quot;,
gt; quot;A!B12 to B!A2quot;, quot;A!B18 to B!A3quot; etc. etc. I know I can manually
gt; reference
gt; each target cell in Worksheet A to transfer over to Worksheet B, but the
gt; scope of this project is such that I would like to write a
gt; function/command
gt; that can then be copied and pasted. A normal copy and paste is dynamic
gt; one
gt; row at a time and I need it to reference every 6th row.
gt;
gt; Sorry for the quot;less than eloquentquot; description of my problem.
gt;
Thank you, thank you. Worked perfectly!

quot;Bernard Liengmequot; wrote:

gt; In A1 of sheet B enter =INDIRECT(quot;A!Bquot;amp;ROW(A1)*6)
gt; Copy this down the column
gt; best wishes
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; quot;FishHeadquot; gt; wrote in message
gt; ...
gt; gt; Is there a way to write a command that will transfer the value of every
gt; gt; quot;6thquot;
gt; gt; cell of a column in worksheet quot;Aquot; to a contiguous column of cells in
gt; gt; worksheet quot;Bquot;? For example the value in quot;A!B6quot; is transferred to quot;B!A1quot;,
gt; gt; quot;A!B12 to B!A2quot;, quot;A!B18 to B!A3quot; etc. etc. I know I can manually
gt; gt; reference
gt; gt; each target cell in Worksheet A to transfer over to Worksheet B, but the
gt; gt; scope of this project is such that I would like to write a
gt; gt; function/command
gt; gt; that can then be copied and pasted. A normal copy and paste is dynamic
gt; gt; one
gt; gt; row at a time and I need it to reference every 6th row.
gt; gt;
gt; gt; Sorry for the quot;less than eloquentquot; description of my problem.
gt; gt;
gt;
gt;
gt;