Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 176
Default Cell Addressing

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
  #2   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Cell Addressing

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"John Calder" wrote:
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

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,069
Default Cell Addressing

You could fill the formula down in column b, copy it, in cell c2 use paste
special and transpose and then delete column b
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)


"John Calder" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,886
Default Cell Addressing

Hi

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

--
Regards

Roger Govier


"John Calder" wrote in message
...
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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell addressing Mike Excel Worksheet Functions 0 November 30th 06 03:51 PM
Cell addressing John Excel Worksheet Functions 0 November 30th 06 01:40 PM
Cell addressing Mike Excel Worksheet Functions 0 November 30th 06 10:02 AM
VBA Cell Addressing Bill Martin -- (Remove NOSPAM from address) Excel Discussion (Misc queries) 5 March 16th 05 10:51 PM
Indexing / Cell Addressing A_n_d_e_r_s Excel Discussion (Misc queries) 1 February 10th 05 12:47 PM


All times are GMT +1. The time now is 02:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"