Formula copies auto when we go vertical but what about go vertical and take data from horizontal?

[cadandcode]

Hi, I manage and edit more than 2 different sheets from different files. When we drag cell vertical down, it copies formula and automatically perform process. (like A1=B300, A2=301 and so) But how can I take datas from in horizontal condition to vertical condition? (like A1=B300, A2=C300, A3=D300)
Example;

sheet 1	A	sheet 2	A	B
1	=pinkcar1 (=A1 in sheet 2)	1	=pinkcar1	=pinkcar2
2	=pinkcar3 (=A1 in sheet 2)	2	=pinkcar3
3	=pinkcar2 (=B1 in sheet 2)	3
4		4

 

[James006]

OK...
If you only have one row in Sheet 2.... it is much easier and in sheet1, in cell A1, you can test:

=OFFSET(Sheet2!$B$1,0,ROW()-1)

 

[James006]

Is the simple formula allowing to make a progress ?

 

[cadandcode]

Is the simple formula allowing to make a progress ?

James it is not working, nevermind this topic anymore. Thank you.

 

[James006]

As I said earlier ... not working is not very precise ... always better to explain the result you get ... and explain why it is not the result you want ...

But I understand you have decided to give up

 

[cadandcode]

İmagine you have things to fill cells.
Like row 1 is, a b c d e and so.
Like row 2 is, 1 2 3 4 5 and so.
Now let me change rows with columns eachother.
Make column A is going down a b c d e and so
Make column B is going down 1 2 3 4 5 and so.

Or let me share a part of worksheet with you.

Kitap3.xlsx.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	1	a	q	.				1	2	3	4	5	6	7	8
2	2	b	w	.				a	b	c	d	e	f	g	h
3	3	c	e	.				q	w	e	r	t	y	u	i
4	4	d	r					.	.	.
5	5	e	t
6	6	f	y			I need a formula which takes yellow ones to place in C column in red ones shape.
7	7	g	u			There is like more than 400 rows in the file that yellow comes from
8	8	h	i			So the file (red column one) will have the rows (like yellow one) in columns.
9						Whenever I put numbers in yellow part, it will go to other file in going down sorting.
10						In other words, I want to select C1 to C8 and drag it to D1 to D8, I expect yellow ones appear there like red ones.
Sayfa1

 

[cadandcode]

As I said earlier ... not working is not very precise ... always better to explain the result you get ... and explain why it is not the result you want ...

But I understand you have decided to give up

Forgot to mention you.

 

[James006]

May be the problem is only when you say .... Drag ....

With your very last example ... let me make two proposals :

1. In Cell D2 =OFFSET($H$3,0,ROW()-1) and you copy down

2. In Cell H4 =OFFSET($C$1,COLUMN()-8,0) and copy to right

Let me have your comments

 

[cadandcode]

May be the problem is only when you say .... Drag ....

I didnt understand what you mean sir, I am trying to do the thing I described above.

 

[James006]

Have you used the two formulas .... in the modified post # 17 ....

 

[cadandcode]

Have you used the two formulas .... in the modified post # 17 ....

This worked.
"D1 =OFFSET($H$3,0,ROW()-1) and you copy down"
But I want to drag the columns to right in left table to get right table data.
I am trying to get right table's rows as columns in left table.
Once I use your formula to put rows in column, I want to drag that column to right and make formula take next row.

Kitap3.xlsx.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	1	a	q	q				1	2	3	4	5	6	7	8
2	2	b	w	w				a	b	c	d	e	f	g	h
3	3	c	e	e				q	w	e	r	t	y	u	i
4	4	d	r	r				.	.	.
5	5	e	t	t
6	6	f	y	y
7	7	g	u	u
8	8	h	i	i
Sayfa1

Cell Formulas
Range	Formula
D1:D8	D1	=OFFSET($H$3,0,ROW()-1)

I want to see . . . instead of same with last one.