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]

Glad to hear the first formula worked

Have you tested : In Cell H4 =OFFSET($C$1,COLUMN()-8,0) and copy to right

 

[cadandcode]

Yes it took from left table and put there qwerty but this is not what I need. I am not going to put datas from left to right table.
I always take data from right table rows to put them in left table as columns.

 

[cadandcode]

James this is what I want to do.
Formula in red worked and took yellow data and put it in column going down.
Let me select red area, drag it right and so that formula takes data from blue and put in green area.
I want to put that formula in red for once only and be able to drag to right. So it always takes rows from right table (from another file) and put them in left table as rows to columns.

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				car	pink	dog	0	cat	Monday	james	excel
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
C1:D8	C1	=OFFSET($H$3,0,ROW()-1)

 

[James006]

OK....

So let's change the formula in your cell C1:

=OFFSET(INDIRECT("H"&COLUMN()),0,ROW()-1)

and you can copy it down till C8

then, you can select the range C1:C8 ... and drag it to Column D ... to get your result ....

 

[cadandcode]

OK....

So let's change the formula in your cell C1:

=OFFSET(INDIRECT("H"&COLUMN()),0,ROW()-1)

and you can copy it down till C8

then, you can select the range C1:C8 ... and drag it to Column D ... to get your result ....

Worked thank you but of course with my poor English, there are other things I need to add but this is enough for you. Thank you.

 

[James006]

Glad to hear it is finally working ...

İngilizceniz kötü değil ...

If your next question is about the source which would be in a different worksheet, you only need to insert the Sheet Name and the Column Letter in the Indirect () section ...

 

[cadandcode]

Glad to hear it is finally working ...

İngilizceniz kötü değil ...

If your next question is about the source which would be in a different worksheet, you only need to insert the Sheet Name and the Column Letter in the Indirect () section ...

Teşekkür ederim