Cell Addressing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Calder - 09 Mar 2007 00:57 GMT

Hi

Is there a way to have a formula that is copied across colums refer to the
source colum (which has data in each row) as "absolute" but "relative" to the
rows on the source column?

Example

Col A, Row 2 = 1
Col A, Row 3 = 2
Col A, Row 4 = 3
Col A, Row 5 = 4

Col B, Row 2 = =$A2+1

When I copy the formula "across" the columns column A stays absolute but the
row stays at row 2. I would like it to change to row 3 as the source.

Any help would be much appreciated

Reply to this Message

Max - 09 Mar 2007 01:08 GMT

One way, albeit using the volatile OFFSET ..

In B2: =OFFSET($A2,COLUMN(A1),)
Copy across as needed

Do hang around awhile for better non volatile options from others to stream
in here

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hi
>
[quoted text clipped - 15 lines]
>
> Any help would be much appreciated

Reply to this Message

john - 09 Mar 2007 10:04 GMT

You could fill the formula down in column b, copy it, in cell c2 use paste
special and transpose and then delete column b

Signature

John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

> Hi
>
[quoted text clipped - 15 lines]
>
> Any help would be much appreciated

Reply to this Message

Roger Govier - 09 Mar 2007 12:32 GMT

Hi

Try
=INDEX($A:$A,COLUMN(C1))

Signature

Regards

Roger Govier

> Hi
>
[quoted text clipped - 18 lines]
>
> Any help would be much appreciated

Reply to this Message