| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this designed for Offset Function?
I wish to populate formulas in cells A2:A20 in Sheet2, the formula's in each
of these cells is simply a reference to Sheet1 A10; A19; A28 etc. I can't just copy whatever I enter in A2 Sheet2 down as it "skips" 9 rows each time. Could I use a simple Offset formula for this and secondly how would I do so? If anyone could explain the logic of it I'd appreciate it I could of course just manually enter the formulas, but I've got to do the same for columns B to L Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is this designed for Offset Function?
If I understand your question correctly, you could use absolute referencing. Modify the references within the Cells from A10 to $A$10. This "stickies" the reference and you can copy the formula anywhere and the references will remain the same. You could also do $A10 or A$10 so that either the column or row references become absolute when you copy. Hope this helps. -- ronlim ------------------------------------------------------------------------ ronlim's Profile: http://www.excelforum.com/member.php...o&userid=35738 View this thread: http://www.excelforum.com/showthread...hreadid=555216 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is this designed for Offset Function?
Thanks Ronlim
Not quite, my Sheet2 formula cells are continuous, whereas the source on Sheet1 are not continuous, thus Sheet1A1 will be referenced to a formula in Sheet2A1 Sheet1A10 will be referenced to a formula in Sheet2A2 Sheet1A19 will be referenced to a formula in Sheet2A3 etc I just want to copy my Formula in Sheet2A1 down (easily) "ronlim" wrote in message ... If I understand your question correctly, you could use absolute referencing. Modify the references within the Cells from A10 to $A$10. This "stickies" the reference and you can copy the formula anywhere and the references will remain the same. You could also do $A10 or A$10 so that either the column or row references become absolute when you copy. Hope this helps. -- ronlim ------------------------------------------------------------------------ ronlim's Profile: http://www.excelforum.com/member.php...o&userid=35738 View this thread: http://www.excelforum.com/showthread...hreadid=555216 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is this designed for Offset Function?
"John" wrote in message
... Thanks Ronlim Not quite, my Sheet2 formula cells are continuous, whereas the source on Sheet1 are not continuous, thus Sheet1A1 will be referenced to a formula in Sheet2A1 Sheet1A10 will be referenced to a formula in Sheet2A2 Sheet1A19 will be referenced to a formula in Sheet2A3 etc I just want to copy my Formula in Sheet2A1 down (easily) Yes, OFFSET sounds like a good solution. =OFFSET(Sheet1!A$1,9*(ROW()-1),0) -- David Biddulph |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is this designed for Offset Function?
Thanks David, thats what I'm after
"David Biddulph" wrote in message ... "John" wrote in message ... Thanks Ronlim Not quite, my Sheet2 formula cells are continuous, whereas the source on Sheet1 are not continuous, thus Sheet1A1 will be referenced to a formula in Sheet2A1 Sheet1A10 will be referenced to a formula in Sheet2A2 Sheet1A19 will be referenced to a formula in Sheet2A3 etc I just want to copy my Formula in Sheet2A1 down (easily) Yes, OFFSET sounds like a good solution. =OFFSET(Sheet1!A$1,9*(ROW()-1),0) -- David Biddulph |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is this designed for Offset Function?
This is more robust:
=INDEX(Sheet1!A:A,(ROWS($1:1)-1)*9+1) Doesn't use volatile functions and isn't row dependent. Also, row insertions won't cause problems! Biff .. "David Biddulph" wrote in message ... "John" wrote in message ... Thanks Ronlim Not quite, my Sheet2 formula cells are continuous, whereas the source on Sheet1 are not continuous, thus Sheet1A1 will be referenced to a formula in Sheet2A1 Sheet1A10 will be referenced to a formula in Sheet2A2 Sheet1A19 will be referenced to a formula in Sheet2A3 etc I just want to copy my Formula in Sheet2A1 down (easily) Yes, OFFSET sounds like a good solution. =OFFSET(Sheet1!A$1,9*(ROW()-1),0) -- David Biddulph |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is this designed for Offset Function?
Just to do exactly what the OP requested:
=INDEX(Sheet1!A:A,9*ROWS($1:1)+1) -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Biff" wrote in message ... This is more robust: =INDEX(Sheet1!A:A,(ROWS($1:1)-1)*9+1) Doesn't use volatile functions and isn't row dependent. Also, row insertions won't cause problems! Biff .. "David Biddulph" wrote in message ... "John" wrote in message ... Thanks Ronlim Not quite, my Sheet2 formula cells are continuous, whereas the source on Sheet1 are not continuous, thus Sheet1A1 will be referenced to a formula in Sheet2A1 Sheet1A10 will be referenced to a formula in Sheet2A2 Sheet1A19 will be referenced to a formula in Sheet2A3 etc I just want to copy my Formula in Sheet2A1 down (easily) Yes, OFFSET sounds like a good solution. =OFFSET(Sheet1!A$1,9*(ROW()-1),0) -- David Biddulph |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
| Date & Time | New Users to Excel | |||
| Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
| Conversion | Excel Worksheet Functions | |||
| HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Hybrid Mode
