I am stumped on a linking problem...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Back
Top