16,001,934 members
See more:
Hello Experts,

To postulate this relatively simple issue i am going to use an example:

Let us assume that in Sheet 2 the required data is the the following cells: C4,H4,M4,R4......etc

I would like to show this data in Sheet 1 A1,B1,C1,D1.......etc

I have tried to input the first 3 cells manually, and dragging the cells, hoping that it would repeat the pattern, but this was useless.

Posted

## Solution 1

Try this formula in the Sheet 1 cells...
`=OFFSET(Sheet2!\$C1, 0, 5 * (COLUMN()-1))`

By way of explanation `COLUMN()` will return the column number for the cell containing the formula so for
```A1   B1   C1   D1
1    2    3    4     etc...```

The data you want is every 5th column (once the pattern starts) hence the `5 * `

`OFFSET` returns the value from the cell (in this case) `0` rows away from the initial starting point (C1) and the calculated number of columns away from the same starting cell.

The `-1` is because in A1 I want the value of the starting cell and the `\$C1` is because I always want to calculate from the same point (otherwise Excel will change this to D, E etc as you drag the formula across)

Edit - changed the sheet number to the correct one!! Sorry about that.

v2
Maciej Los 14-Jan-15 13:09pm
+5
Gilmore Sacco 15-Jan-15 5:28am
Hello Chill60. Thanks for the info.

I managed to arrive at the same conclusion yesterday, but you help was greatly appriecated and usefull to help me confirm taht i used the correct method.

Basically the offset function was used :
OFFSET(Reference, Row displacement, Colum displacement as an Arithmetic series,)
CHill60 15-Jan-15 5:30am
Good stuff! I think the key bit (for the benefit of any other readers) is the COLUMN() function which allows you to create that arithmetic series quite easily.
Glad to have helped

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 Richard Deeming 80 merano99 40 Yvan Rodrigues 20 Dave Kreskowiak 15 brownpeteg 10
 OriginalGriff 631 Richard Deeming 320 Dave Kreskowiak 286 Pete O'Hanlon 270 CPallini 120

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900