Posting formula horizontally

cb123

New Member
Joined
Jul 1, 2016
Messages
34
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.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
Place this formula in cell A1 on sheet 2, and copy across:
Excel Formula:
=OFFSET(Sheet1!$Q$1,COLUMN()-1,0)
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,543
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=INDEX(Sheet1!$Q$1:$Q$100,COLUMNS($A1:A1))
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,059
Office Version
  1. 2016
Platform
  1. Windows
=INDIRECT(ADDRESS(COLUMN(),17,,,"sheet1"))
 

cb123

New Member
Joined
Jul 1, 2016
Messages
34

ADVERTISEMENT

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
Joined
Jul 1, 2016
Messages
34
=INDIRECT(ADDRESS(COLUMN(),17,,,"sheet1"))
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
Joined
Aug 4, 2017
Messages
1,059
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
A1:J1A1=INDIRECT(ADDRESS(COLUMN(),17,,,"sheet2"))
D2:M2D2=INDIRECT(ADDRESS(COLUMN()-3,17,,,"sheet2"))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,543
Office Version
  1. 365
Platform
  1. Windows
Have you tried the formula I suggested which doesn't involve any volatile functions?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,543
Office Version
  1. 365
Platform
  1. Windows
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)
A3:Z3A3=INDIRECT(ADDRESS(COLUMN(),17,,,"sheet1"))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,567
Messages
5,625,547
Members
416,116
Latest member
Joemamasuka

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top