(=sheet!) drag horizontally, column increment issues

Zeroh

New Member
Joined
Dec 31, 2019
Messages
5
Office Version
365
Platform
Windows
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?
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Zeroh

New Member
Joined
Dec 31, 2019
Messages
5
Office Version
365
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,743
Office Version
365
Platform
Windows
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

New Member
Joined
Dec 31, 2019
Messages
5
Office Version
365
Platform
Windows
Alright I'll try.

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

- 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:
1577886818752.png


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:
1577886878168.png


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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,743
Office Version
365
Platform
Windows
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

New Member
Joined
Dec 31, 2019
Messages
5
Office Version
365
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,743
Office Version
365
Platform
Windows
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

New Member
Joined
Dec 31, 2019
Messages
5
Office Version
365
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,743
Office Version
365
Platform
Windows
You are welcome.
Glad I was able to help!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,826
Messages
5,489,081
Members
407,671
Latest member
annep833

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top