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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 01:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com