Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell addressing | Excel Worksheet Functions | |||
Cell addressing | Excel Worksheet Functions | |||
Cell addressing | Excel Worksheet Functions | |||
VBA Cell Addressing | Excel Discussion (Misc queries) | |||
Indexing / Cell Addressing | Excel Discussion (Misc queries) |