help linking to data and filling down or across (transpose)

J

jimboy

Well-known Member

Joined

Apr 11, 2002

Messages

2,314

• Jan 30, 2004

• #2

Re: help linking to data and filling down or across (transpo

Use the offset function.

If you data is in row 1 i.e. A1, B1, C1 etc and you want to enter this in E1 to E3 enter this formula into E1 and copy down;

Code:

=OFFSET($A$1,0,ROW()-1,1,1)

If you want to enter it into E10 you will need to amend the row()-1 part to;

Code:

=OFFSET($A$1,0,ROW()-10,1,1)

 

Upvote 0

R

roger

New Member

Joined

Feb 25, 2002

Messages

6

• Jan 30, 2004

• #3

Re: help linking to data and filling down or across (transpo

thanks! It worked!

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Jan 30, 2004

• #4

Re: help linking to data and filling down or across (transpo

Also, with non-volatile INDEX, robust against inserting rows before the first formula cell:

Book5
	A	B	C	D
1	5	6	7
2
3	5
4	6
5	7
6
7
Sheet1

The formula in A3 is:

=IF(ROW()-ROW($A$3)+1<=COUNT($A$1:$C$1),INDEX($A$1:$C$1,ROW()-ROW($A$3)+1),"")

where A3 in the ROW($A$3) bit is the adaptable start cell.

 

Upvote 0

C

Captain Smith

Active Member

Joined

Feb 28, 2003

Messages

324

• Feb 13, 2004

• #5

Re: help linking to data and filling down or across (transpo

What formula will do the opposite of this? I have text going down and I want to "transpose" it by dragging a formula across a row?

Thanks

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Feb 13, 2004

• #6

Re: help linking to data and filling down or across (transpo

Captain Smith said:

What formula will do the opposite of this? I have text going down and I want to "transpose" it by dragging a formula across a row?

Thanks

Click to expand...

Book4
	A	B	C	D	E
1		fra	xza	gda	vxa
2	fra
3	xza
4	gda
5	vxa
6
Sheet1

The formula in B1, which is copied across, is:

=IF(COLUMN()-COLUMN($B$2)+1<=MATCH(REPT("z",255),$A$2:$A$5),INDEX($A$2:$A$5,COLUMN()-COLUMN($B$2)+1),"")

 

Upvote 0

C

Captain Smith

Active Member

Joined

Feb 28, 2003

Messages

324

• Feb 13, 2004

• #7

Re: help linking to data and filling down or across (transpo

Thank you!

 

Upvote 0