Posting formula horizontally

[cb123]

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]

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
1	A
2	B
3	C
4	D
5	E
6	F
7	G
8	H
9	I
10	J
11	K
12	L
13	M
14	N
15	O
16	P
17	Q
18	R
19	S
20	T
21	U
22	V
23	W
24	X
25	Y
26	Z
Sheet1

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
2	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
3	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
Sheet2

Cell Formulas
Range	Formula
A1:Z1	A1	=INDEX(Sheet1!$Q$1:$Q$100,COLUMNS($A1:A1))
A2:Z2	A2	=OFFSET(Sheet1!$Q$1,COLUMN()-1,0)
A3:Z3	A3	=INDIRECT(ADDRESS(COLUMN(),17,,,"sheet1"))

[

Cell Formulas
Range	Formula

 

[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]

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:

=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]

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.