# Posting formula horizontally

#### cb123

##### New Member
Hi all, hoping someone can help me out and save me a lot of time on this one! If you can many thanks in advance.

I have a workbook and in it sheet1. Sheet1 hash data which is collected vertically, so let's say that data is in Q1, down to Q10.

then i have another sheet, sheet2, which needs to reference these cells (There are hundreds of them travelling down Q in sheet1). The issue is they need to reference them horizontally. So in sheet2 cell A1 is linked to Q1 in sheet1. I would like to copy that formula across into B1 (referencing Q2) and then C1 (referencing Q3) and so forth but whenever i try this it uses R1 and then S1 etc from sheet1.

I want the column (Q) to remain the same but the cell reference to increase by one each time.

Many thanks.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### Joe4

Place this formula in cell A1 on sheet 2, and copy across:
Excel Formula:
``=OFFSET(Sheet1!\$Q\$1,COLUMN()-1,0)``

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=INDEX(Sheet1!\$Q\$1:\$Q\$100,COLUMNS(\$A1:A1))``

#### cb123

##### New Member

Place this formula in cell A1 on sheet 2, and copy across:
Excel Formula:
``=OFFSET(Sheet1!\$Q\$1,COLUMN()-1,0)``
Hi thanks for the advice, that works but the use of the \$ means the cell references stay the same. As I copy across I want the column Q to remain the same but for the cell reference to increase by 1 each time So Q1, Q2, etc. Would you know how i can remedy that?

#### cb123

##### New Member
Thanks for the advice i just tried this one and it didn't work for me. I am quite a novice on these things though. I worked out that 17 is a reference to the Q column and i renamed sheet1 accordingly but is there anything else in your formula which i need to change to make specific? Many thanks.

#### mart37

##### Well-known Member

Indeed 17 is column Q. Column() is the rownumber. Sheet1 is the sheetname.
Example: sheet2 have in Q1:Q10 1 till 10
When you place the formula in column A then COLUMN() =1
When you place the formula in column D then COLUMN() =4
When you want 1 in column D you have to subtract with 3 --> 4 - 3 = 1
Map1
ABCDEFGHIJKLM
112345678910
212345678910
sheet1
Cell Formulas
RangeFormula

#### Fluff

##### MrExcel MVP, Moderator
Have you tried the formula I suggested which doesn't involve any volatile functions?

#### Joe4

Hi thanks for the advice, that works but the use of the \$ means the cell references stay the same. As I copy across I want the column Q to remain the same but for the cell reference to increase by 1 each time So Q1, Q2, etc. Would you know how i can remedy that?
I am guessing that you have not tried my formula, because if you had, you would have seen that it does exactly what you want.
Be careful about picking apart certain parts of formulas you do not understand and making assumptions about them based on that.
And I recommend at least trying someone's formula before commenting on it. It only takes a few seconds, much less time than it took that person to make the formula for you.

All that you are locking down is the "starting point" Q1. Then the OFFSET function is working off of that.
The OFFSET function says given some starting point, move "x" rows to the left/right and "y" columns up/down.
The COLUMN() function returns the column number that the formula exists in. So putting the COLUMN() function in column A returns 1, putting it in column B returns 2, etc.
So, as we move across, this keeps incrementing by 1. So we can make use of that to tell the OFFSET function to move that many of rows down.

Try it, and you will see.

#### Fluff

##### MrExcel MVP, Moderator
All 3 solutions will do what you have asked for.
+Fluff 1.xlsm
Q
1A
2B
3C
4D
5E
6F
7G
8H
9I
10J
11K
12L
13M
14N
15O
16P
17Q
18R
19S
20T
21U
22V
23W
24X
25Y
26Z
Sheet1

Cell Formulas
RangeFormula
A1:Z1A1=INDEX(Sheet1!\$Q\$1:\$Q\$100,COLUMNS(\$A1:A1))
A2:Z2A2=OFFSET(Sheet1!\$Q\$1,COLUMN()-1,0)

Replies
19
Views
392
Replies
5
Views
120
Replies
22
Views
1K
Replies
2
Views
299
Replies
5
Views
249

1,129,330
Messages
5,635,647
Members
416,870
Latest member
rikimon2

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back