linking a row of cells to a vertical column

  • Thread starter Thread starter melvin
  • Start date Start date
M

melvin

Hi folks,

I am trying to link a row of cells to a vertical column.

For example, I want to link cell A1 to cell A10; cell A2 to cell B10;
cell A3 to cell C10; and so on... however I can only seem to link A1
to A10; A2 to A11; etc.. OR A1 to A10; B1 to B10; etc..

I have tried fixing the columns and rows using "$" but I can't get a
horizontal row to link vertically so I have reverted to copying and
pasting the transposed form.

I would greatly appreciate any suggestions on how to achieve this
linking pattern.

Many thanks
 
Hi
enter the following formula in A10
=OFFSET($A$1,COLUMN()-1,0)
and copy to the right
 
In A10 enter & copy across as far as needed:

=INDEX($A$1:$A$9,COLUMN()-COLUMN($A$10)+1)
 
Frank,

Many thanks for your help (AGAIN!). The formula I was looking for wa
the reciprocal of what you gave me:

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

Cheers for sharing the inf
 
melvin > said:
Frank,

Many thanks for your help (AGAIN!). The formula I was looking for was
the reciprocal of what you gave me:

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

Cheers for sharing the info
Click to expand...


Hi
hopefully Aladin now doesn't get confused as you responded to his
(quite similar and IMHO better) solution

Frank
 
Frank/Aladdin,

Thanks for both your contributions so far.

The OFFSET formula has provided the necessary link to create a colum
of information relating to a row of information.

I am now looking at integrating this function with a SUM calculation.
For example, in A10 I want to sum (AA10:AA100); and in A11 su
(AB10:AB100); in A12 sum (AC10:AC100); and so on...

How/Where would I refer to this in the OFFSET function when copyin
downward?

I am very grateful for your help so far.

kind regards
 
Back
Top