Formula to copy data from every 4th column
 

Hi

I am trying to create a formula to copy data from one workbook to another.
The problem I have is that I want to copy only relevant data which is in
every 4th column.

So in cell A1 (destination sheet) I copy from A1 then A2 I want to copy from
A6, then A3 I want to copy from A10 and so on. Then the same for numerous
rows.

I need a formula that I can drag across so that I don't have to type a
formula for each instance because I will be using all available columns.

Hope that makes sense and someone can help. Thanks in anticipation!

Formula to copy data from every 4th column
 

a1=INDIRECT("sheet1!a"&2+(ROW()-1)*4) copy down the formula
it copies the value of sheet1-a2 first, sheet1-a6, a10, ..
with regards
sreedhar

"HighlandRoss" wrote:

Hi

I am trying to create a formula to copy data from one workbook to another.
The problem I have is that I want to copy only relevant data which is in
every 4th column.

So in cell A1 (destination sheet) I copy from A1 then A2 I want to copy from
A6, then A3 I want to copy from A10 and so on. Then the same for numerous
rows.

I need a formula that I can drag across so that I don't have to type a
formula for each instance because I will be using all available columns.

Hope that makes sense and someone can help. Thanks in anticipation!

Formula to copy data from every 4th column
 

Thanks Sreedhar.

Sorry I made a mistake. My example should state (i'll be specific this time
as well)

F6 (destination cell) = BY7, G6 = CC7, H6= CG7

The book I am pulling data from is called ABC 2008.08.05 Resource Book and
the sheet is Module 2.

When I tried the formula you gave me it flagged a "Name" error.

"yshridhar" wrote:

a1=INDIRECT("sheet1!a"&2+(ROW()-1)*4) copy down the formula
it copies the value of sheet1-a2 first, sheet1-a6, a10, ..
with regards
sreedhar

"HighlandRoss" wrote:

Hi

I am trying to create a formula to copy data from one workbook to another.
The problem I have is that I want to copy only relevant data which is in
every 4th column.

So in cell A1 (destination sheet) I copy from A1 then A2 I want to copy from
A6, then A3 I want to copy from A10 and so on. Then the same for numerous
rows.

I need a formula that I can drag across so that I don't have to type a
formula for each instance because I will be using all available columns.

Hope that makes sense and someone can help. Thanks in anticipation!

Formula to copy data from every 4th column
 

Try this formula, which can start in *any* cell:

=INDEX(7:7,4*COLUMNS($A:A)+73)

And copy across as needed.

You can add your Path.


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"HighlandRoss" wrote in message
...
Thanks Sreedhar.

Sorry I made a mistake. My example should state (i'll be specific this time
as well)

F6 (destination cell) = BY7, G6 = CC7, H6= CG7

The book I am pulling data from is called ABC 2008.08.05 Resource Book and
the sheet is Module 2.

When I tried the formula you gave me it flagged a "Name" error.

"yshridhar" wrote:

a1=INDIRECT("sheet1!a"&2+(ROW()-1)*4) copy down the formula
it copies the value of sheet1-a2 first, sheet1-a6, a10, ..
with regards
sreedhar

"HighlandRoss" wrote:

Hi

I am trying to create a formula to copy data from one workbook to
another.
The problem I have is that I want to copy only relevant data which is in
every 4th column.

So in cell A1 (destination sheet) I copy from A1 then A2 I want to copy
from
A6, then A3 I want to copy from A10 and so on. Then the same for
numerous
rows.

I need a formula that I can drag across so that I don't have to type a
formula for each instance because I will be using all available columns.

Hope that makes sense and someone can help. Thanks in anticipation!

Formula to copy data from every 4th column
 

Thanks.

Sorry but how do I add the path to that as I have tried and keep getting
warnings!? Also the data I am copying are the results of other formulas
rather than actual values will that make a difference?

thanks for your help it's much appreciated!!

"RagDyeR" wrote:

Try this formula, which can start in *any* cell:

=INDEX(7:7,4*COLUMNS($A:A)+73)

And copy across as needed.

You can add your Path.


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"HighlandRoss" wrote in message
...
Thanks Sreedhar.

Sorry I made a mistake. My example should state (i'll be specific this time
as well)

F6 (destination cell) = BY7, G6 = CC7, H6= CG7

The book I am pulling data from is called ABC 2008.08.05 Resource Book and
the sheet is Module 2.

When I tried the formula you gave me it flagged a "Name" error.

"yshridhar" wrote:

a1=INDIRECT("sheet1!a"&2+(ROW()-1)*4) copy down the formula
it copies the value of sheet1-a2 first, sheet1-a6, a10, ..
with regards
sreedhar

"HighlandRoss" wrote:

Hi

I am trying to create a formula to copy data from one workbook to
another.
The problem I have is that I want to copy only relevant data which is in
every 4th column.

So in cell A1 (destination sheet) I copy from A1 then A2 I want to copy
from
A6, then A3 I want to copy from A10 and so on. Then the same for
numerous
rows.

I need a formula that I can drag across so that I don't have to type a
formula for each instance because I will be using all available columns.

Hope that makes sense and someone can help. Thanks in anticipation!

Formula to copy data from every 4th column
 

If you spelled everything right, and put the spaces in the right places in
your example, this should work:

=INDEX('[ABC 2008.08.05 Resource Book.xls]Module 2'!7:7,4*COLUMNS($A:A)+73)

This of course assumes that both WBs are in the same folder.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"HighlandRoss" wrote in message
...
Thanks.

Sorry but how do I add the path to that as I have tried and keep getting
warnings!? Also the data I am copying are the results of other formulas
rather than actual values will that make a difference?

thanks for your help it's much appreciated!!

"RagDyeR" wrote:

Try this formula, which can start in *any* cell:

=INDEX(7:7,4*COLUMNS($A:A)+73)

And copy across as needed.

You can add your Path.


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"HighlandRoss" wrote in message
...
Thanks Sreedhar.

Sorry I made a mistake. My example should state (i'll be specific this
time
as well)

F6 (destination cell) = BY7, G6 = CC7, H6= CG7

The book I am pulling data from is called ABC 2008.08.05 Resource Book
and
the sheet is Module 2.

When I tried the formula you gave me it flagged a "Name" error.

"yshridhar" wrote:

a1=INDIRECT("sheet1!a"&2+(ROW()-1)*4) copy down the formula
it copies the value of sheet1-a2 first, sheet1-a6, a10, ..
with regards
sreedhar

"HighlandRoss" wrote:

Hi

I am trying to create a formula to copy data from one workbook to
another.
The problem I have is that I want to copy only relevant data which is
in
every 4th column.

So in cell A1 (destination sheet) I copy from A1 then A2 I want to
copy
from
A6, then A3 I want to copy from A10 and so on. Then the same for
numerous
rows.

I need a formula that I can drag across so that I don't have to type
a
formula for each instance because I will be using all available
columns.

Hope that makes sense and someone can help. Thanks in anticipation!