Using Indirect function to skip columns from different worksheet

[AsherS]

A post about skipping rows is very similar to my situation, but I'm stuck on how to translate that for columns. (http://www.mrexcel.com/forum/showthread.php?t=77845)

I'd like to transfer information from a worksheet called "Raw Data" to another sheet, skipping every 5th column. I'd like to go from "Raw Data" B12 to a new sheet C6, then from "Raw Data" G12 to D6 in the new sheet, and so forth.

I figured out that I'll need to use COLUMN()*5-13 as a component of the function, but I can't seem to figure out how to format the name of the worksheet correctly, with the ampersand and quotation marks.

This doesn't work at all, but I think it's in the ballpark...

=INDIRECT("Raw Data!"(COLUMN()*5)-13)&12)

Can anyone help make this work?
Thanks!
Asher

 

[Jonmo1]

Welcome to the board...

You don't need Indirect for this..

Try this in the New Sheet C6, then Filled Right as far as needed.

=INDEX('Raw Data'!12:12,(COLUMNS($A1:A1)-1)*5+2)

Don't change COLUMNS($A1:A1) - it has nothing to do with location of data.
The 5 is the inteveral (every 5th column)
The 2 is the column to begin with (A=1, B=2,C=3 etc..)

Hope that helps.

 

[AsherS]

Wow! Thanks!! That worked right away. I can't believe I've never used this Forum before. I'm astounded to have a successful reply in just a few minutes. Amazing!!

 

[Jonmo1]

Glad to help

Incidentally, I had a typo in there, I'm assuming you figured it out..

=INDEX('Raw Data'!12:12,(COLUMNS($A1:A10)-1)*5+2)
Should have been
=INDEX('Raw Data'!12:12,(COLUMNS($A1:A1)-1)*5+2)

 

[dderouc]

Glad to help

Incidentally, I had a typo in there, I'm assuming you figured it out..

=INDEX('Raw Data'!12:12,(COLUMNS($A1:A10)-1)*5+2)
Should have been
=INDEX('Raw Data'!12:12,(COLUMNS($A1:A1)-1)*5+2)

Hi
I am trying to use the formula above and it works if i change the last digit manually.
Although my source data is in a other worksheet in columns moving across --the format of the target data where i need the data to appear is in a column going down.. how can i copy the fomula across skip 4 counts?


=INDEX(TaskData!13:13,(COLUMNS($A$1:$A$1)-1)*5+38) is in column one

=INDEX(TaskData!13:13,(COLUMNS($A$1:$A$1)-1)*5+42) is in column two

=INDEX(TaskData!13:13,(COLUMNS($A$1:$A$1)-1)*5+46) is in column three

I need column 4 to read
=INDEX(TaskData!13:13,(COLUMNS($A$1:$A$1)-1)*5+50) without me manually adding in the 50

 

[Jonmo1]

Try this in the first cell, and fill to the right.

=INDEX(TaskData!13:13,(COLUMNS($A$1:A$1)-1)*4+38)

 

[dderouc]

Thank you however if i add =INDEX(TaskData!13:13,(COLUMNS($A1:$A1)-1)*4+38) in one cell I get the number 11 which is accurate however if i drag the formula across to the other cells the correct number is not populating because it needs to skip 4 columns to get number 49. If I manually add the +42 I get the right number.
=INDEX(TaskData!13:13,(COLUMNS($A1:$A1)-1)*4+42)

5/2/2015 5/9/2015 5/16/2015 5/23/2015
11 49


how can i automatically adjust the last digit to add 4?

Hi
I am trying to use the formula above and it works if i change the last digit manually.
Although my source data is in a other worksheet in columns moving across --the format of the target data where i need the data to appear is in a column going down.. how can i copy the fomula across skip 4 counts?


=INDEX(TaskData!13:13,(COLUMNS($A$1:$A$1)-1)*5+38) is in column one

=INDEX(TaskData!13:13,(COLUMNS($A$1:$A$1)-1)*5+42) is in column two

=INDEX(TaskData!13:13,(COLUMNS($A$1:$A$1)-1)*5+46) is in column three

I need column 4 to read
=INDEX(TaskData!13:13,(COLUMNS($A$1:$A$1)-1)*5+50) without me manually adding in the 50

 

[Jonmo1]

That's not the formula I posted.

=INDEX(TaskData!13:13,(COLUMNS($A1:$A1)-1)*4+38)

Look very closely.

Try this in the first cell, and fill to the right.
=INDEX(TaskData!13:13,(COLUMNS($A$1:A$1)-1)*4+38)

 

[Jonmo1]

Also notice from my original post #2

=INDEX('Raw Data'!12:12,(COLUMNS($A1:A1)-1)*5+2)

Don't change COLUMNS($A1:A1) - it has nothing to do with location of data.

 

[dderouc]

Thank you !! Thank you ! that worked (of course you already knew that)!!!
I appreciate your patience!