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.

cb123

New Member
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.
Thanks and i did try it before commenting and could
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)
[
Cell Formulas
RangeFormula

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Joe4

Thanks and i did try it before commenting and could
Then do you see that they all do what you asked for?

If something is not working quite right, please provide us with an example and show us how it is not working.

cb123

New Member
Thanks all i have figured it out now. Even though my data was in, as an example, Q10 the offset formula would only work if i set the fixed starting point 3 cells before - so Q7. Maybe that is because my data starts in sheet2 in column D.

Thanks for your time. As an aside i did try all the formula before making my comments. It was more a case of my knowledge level making it harder for me to understand the solutions.

Joe4

Thanks all i have figured it out now. Even though my data was in, as an example, Q10 the offset formula would only work if i set the fixed starting point 3 cells before - so Q7. Maybe that is because my data starts in sheet2 in column D.

Thanks for your time. As an aside i did try all the formula before making my comments. It was more a case of my knowledge level making it harder for me to understand the solutions.
That is a common issue/mistake many people make. They often "oversimplify" their problem, and provide an example that doesn't quite match their data structure.
Then, when they try to apply it to their actual worksheet, it doesn't work, because they do not know how to adjust the formula accordingly.
I usually advise people not to oversimplify the question or data structure for our sake. If you truly want a "plug-and-play" solution, make the details of your examples pretty much match exactly what you are working with.

With my formula:
Excel Formula:
``=OFFSET(Sheet1!\$Q\$1,COLUMN()-1,0)``
there are really only two things you need to mess with:
1. The starting point (\$Q\$1 in this example). Always make this match the first entry in your list.
2. The "adjustment" to the offset, which is the "-1" in the "COLUMN()-1" part of the formula. This will always be a + or - some number. Once you find the number, it will be the same for every formula, so you don't need to adjust it after finding the correct initial value, just drag across.

If you are having trouble understanding how that second part works, it is easiest to see with an example.
If you tell me what the address of the first cell you are trying to pull is (is it Q1, is it Q7, is it Q10?) and let me know what cell address this first formula is going in (is it A1? is it B1?), I can walk you through how to figure out that value for part 2.

cb123

New Member
Thanks for your help, i'm all sorted now by changing the -1 to -4 and i can now go back to referencing the actual cell the data comes from in the first instance.

Thank you also for the advice regarding explaining the actual example, i will ensure to do so in the future to save time all round.

All the best.

Joe4

You are welcome.
Glad we got it all working out the way you need.

Replies
19
Views
366
Replies
5
Views
99
Replies
22
Views
1K
Replies
2
Views
279
Replies
5
Views
201

1,126,996
Messages
5,622,079
Members
415,875
Latest member
Tarali

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.

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