(=sheet!) drag horizontally, column increment issues

[Zeroh]

Okay so I've been struggling with this for a while and I can't seem to formulate my question that great either, bear with me please.

So, I have created an excel sheet that collects exercise data into columns extending for several weeks.
In my data sheet I am trying to collect these data points into an organized table. I'll provide with an example to explain the issue:

Column A has the data at row 100. Column F has the next set of data at the same row, 100. This extends for 52 data points, each with a 5 column gap.
Essentially this 5 column gap between each data point makes it furiously hard to collect the data in the other sheet. I keep trying to use the (=sheet!X100), which works fine. But when i drag horizontally the increment only follows 1 column, not 5. Even if i select a series of 10 already referenced points excel cant seem to understand the sequence of rows. Naturally I've been grinding out this plotting each point manually, which at this point makes my eyes bleed as I have over 700 data points to plot...

Is there a way around this that I'm to stupid to see?

 

[Zeroh]

Okay so I've been struggling with this for a while and I can't seem to formulate my question that great either, bear with me please.

So, I have created an excel sheet that collects exercise data into columns extending for several weeks.
In my data sheet I am trying to collect these data points into an organized table. I'll provide with an example to explain the issue:

Column A has the data at row 100. Column F has the next set of data at the same row, 100. This extends for 52 data points, each with a 5 column gap.
Essentially this 5 column gap between each data point makes it furiously hard to collect the data in the other sheet. I keep trying to use the (=sheet!X100), which works fine. But when i drag horizontally the increment only follows 1 column, not 5. Even if i select a series of 10 already referenced points excel cant seem to understand the sequence of rows. Naturally I've been grinding out this plotting each point manually, which at this point makes my eyes bleed as I have over 700 data points to plot...

Is there a way around this that I'm to stupid to see?

Edit: "excel cant seem to understand the sequence of columns"

 

[Joe4]

Okay so I've been struggling with this for a while and I can't seem to formulate my question that great either, bear with me please.

It may make more sense to us if you lay out an example of what you actually want, i.e.
formula at A100, formula at F100, etc.

Many of us are visual people, so seeing what you are after often goes a long way in helping in the explanation (I tend to get lost in long, verbose explanations).

 

[Zeroh]

Alright I'll try.

I want the data from Sheet A located at the following points:

- as you can see there is a column gap between the cells.
- This is a simple representation of my actual sheet which is far more complicated and these gaps cannot be reduced.


To appear in Sheet B at the following points:

To do this, I use the formula: (=SheetA!) to reference what cell I wish to take the data from.
The cells in sheet A are dynamic and Sheet B must be able to change along as Sheet A numbers change, which is why I can't just copy paste the data.

Now, If I select the entire range in sheet B as such:

And try to drag this horizontally to fill inn F2, I get nothing.
This is because excel follows one column, whereas sheet A has a gap of 2 columns between the cells, making F2 represent (sheetA!M1), and not (sheetA1!O1).
As of now my only method to collect the data in sheet B is to manually write in (=Sheet!X) in every.single.cell. Which is frustratingly slow.

Is there a way to drag this horizontally and make excel understand that it needs to extend the formula but also increment the columns?
Are there other formulas that can reference cells in other sheets like this?

 

[Joe4]

Try placing this formula in cell B2 on sheet B and dragging across:
=OFFSET(SheetA!$C$1,ROW()-2,(B$1-1)*3,1,1)

 

[Zeroh]

Try placing this formula in cell B2 on sheet B and dragging across:
=OFFSET(SheetA!$C$1,ROW()-2,(B$1-1)*3,1,1)

This works! Brilliant!
I dont understand what is going on though, could you explain the elements of that formula so that I may apply it to my other document?

 

[Joe4]

The key is understanding how the OFFSET function works. You pick some starting position, then select the number of rows/columns to move over from that starting position.
Check out this explanation of the OFFSET function for a description of details on how it works: OFFSET function

Also note a few other key details:

1. My use of mixed range references, locking some, while allowing others to float as we drag the formulas across the page.
If you are unfamiliar with that, see this: Relative And Absolute Range References

2. Lastly, my use of the ROW() function. This simply returns the row number of whatever row the formula is in (so if this formula is in row 2, it will return 2).

What I recommend doing, is going through the first few cells, and substituting the actual values in to my formula, to see what number my formula is actually using for the offset arguments. Once you see the pattern, I think it should make sense. Note that I am multiplying by 3 since you have data every 3rd column. So if you really have data every 5th column, you would want to change that to a 5 instead.

Feel free to post back if you have any other questions and working through this.

 

[Zeroh]

After reading up on the offset, column and row functions I managed to figure out how this works.
thank you for the help, now I can get this done in a third of the time!

 

[Joe4]

You are welcome.
Glad I was able to help!