Fill Handle Across Columns
 

How do I get cell references to change the number (column) not letter
(row) when I use the fill handle across rows?

e.g. If I have a cell reference in Sheet 1 that refers to A2 in Sheet 2
and I want to use the fill handle to take values from A3, A4, A5 of
Sheet 2 and run them as headings across the columns of Sheet 1 - how do
I do this?

If you just drag the fill handle it uses A2, B2, C2, D2

Thanks


--
SenojNW
------------------------------------------------------------------------
SenojNW's Profile: http://www.excelforum.com/member.php...o&userid=25958
View this thread: http://www.excelforum.com/showthread...hreadid=393746

You need a formula

=OFFSET(Sheet1!$A$1,COLUMN(A:A)-1,)

you can also copy and paste special and select transpose

--
Regards,

Peo Sjoblom

(No private emails please)


"SenojNW" wrote in
message ...

How do I get cell references to change the number (column) not letter
(row) when I use the fill handle across rows?

e.g. If I have a cell reference in Sheet 1 that refers to A2 in Sheet 2
and I want to use the fill handle to take values from A3, A4, A5 of
Sheet 2 and run them as headings across the columns of Sheet 1 - how do
I do this?

If you just drag the fill handle it uses A2, B2, C2, D2

Thanks


--
SenojNW
------------------------------------------------------------------------
SenojNW's Profile:
http://www.excelforum.com/member.php...o&userid=25958
View this thread: http://www.excelforum.com/showthread...hreadid=393746

Hi!

Try this:

=INDIRECT("Sheet2!A"&COLUMN(B:B))

Evaluates to =Sheet2!A2

As you drag copy across the COLUMN() argument will increment leading to:

=Sheet2!A2.....=Sheet2!A3.....=Sheet2!A4.....=Shee t2!A5.....etc

Biff

"SenojNW" wrote in
message ...

How do I get cell references to change the number (column) not letter
(row) when I use the fill handle across rows?

e.g. If I have a cell reference in Sheet 1 that refers to A2 in Sheet 2
and I want to use the fill handle to take values from A3, A4, A5 of
Sheet 2 and run them as headings across the columns of Sheet 1 - how do
I do this?

If you just drag the fill handle it uses A2, B2, C2, D2

Thanks


--
SenojNW
------------------------------------------------------------------------
SenojNW's Profile:
http://www.excelforum.com/member.php...o&userid=25958
View this thread: http://www.excelforum.com/showthread...hreadid=393746

The INDIRECT function seems to work well...

Can you explain the formula a bit more for me - I like to understand
what it is doing... :)


--
SenojNW
------------------------------------------------------------------------
SenojNW's Profile: http://www.excelforum.com/member.php...o&userid=25958
View this thread: http://www.excelforum.com/showthread...hreadid=393746

Hi!

=INDIRECT("Sheet2!A"&COLUMN(B:B))

The Indirect function takes TEXT representations of references and
"converts" them to useable formula references.

In this example the TEXT string "Sheet2!A" is concatenated with the result
of the COLUMN() function to produce the TEXT representation that INDIRECT
can use as a formula reference.

The COLUMN() function returns the column NUMBER of it's argument. In this
case column B is the second column so:

COLUMN(B:B) = 2

You could also express that as:

COLUMN(B1)

The Column function will ignore the ROW reference, in this case, 1.

So, Indirect uses the TEXT string:

"Sheet2!A2"

and "converts" it to the useable formula reference:

=Sheet2!A2

Biff

"SenojNW" wrote in
message ...

The INDIRECT function seems to work well...

Can you explain the formula a bit more for me - I like to understand
what it is doing... :)


--
SenojNW
------------------------------------------------------------------------
SenojNW's Profile:
http://www.excelforum.com/member.php...o&userid=25958
View this thread: http://www.excelforum.com/showthread...hreadid=393746