auto filling columns from rows

[Guest]

Hi there

How can I auto fill a series of values that equals a series of cells from
another sheet?
The cells I want the information to come from a row in another sheet and are
going into a column.
the references of the host cell are
'!AH$9
when I fill down using the plus sign at the bottom corner it just copies
this reference to each cell instead of
filling the values '!AI$9 then '!AJ$9 etc.

 

[Guest]

Ken,
Try taking the dollar signs out before you copy the formula.
The dollar signs make the formula absolute, which means that no matter where
you copy it to, you'll always be referencing the same cell.
This is fine where yu want to, for example, always reference one cell in a
formula that might, for example, contain a currency conversion factor that
you want to apply to a range of cells, but not, I suspect, in what you're
trying to do.
Hope this helps
Pete

 

[Max]

One way

Assuming in Sheet1,
H9:K9 contains the values: 1,2,3,4

In Sheet2
-------------

Put in any starting cell, say A2:

=OFFSET(Sheet1!$H$9,,ROWS($A$1:A1)-1)

A2 will return the same as: =Sheet1!H9
i.e. the value of 1

Copy A2 down to A5

As you copy down,

A3 will return the same as: =Sheet1!I9
A4 will return the same as: =Sheet1!J9
A5 will return the same as: =Sheet1!K9

i.e. A2:A5 will return the values: 1,2,3,4
from Sheet1's H9:K9

Adapt to suit