Fill Handle Across Columns

[SenojNW]

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

 

[Peo Sjoblom]

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)

 

[Biff]

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.....=Sheet2!A5.....etc

Biff

 

[SenojNW]

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...

 

[Biff]

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