Home |
Search |
Today's Posts |
#1
|
|||
|
|||
nonsequential cell references
In a worksheet I have got a set of data where column A contains a week ending
date and then in columns B,C,D etc are the data refering to that week (so A4 is 7/10/04, B4 is 7/17/04 and so on). Elsewhere on the spreadsheet I want to copy every fourth row from the above-mentioned data set, so cell L4= cell A4, then L5= cell A8, L6= cell A12 and so on. Any ideas how to do it in a way that means I can easily drag a formula down, rather than what I have done so far which is to copy every cell and then gone through and deleted 3 weeks, miss a row and then delete the next 3 weeks data etc? Tim. |
#2
|
|||
|
|||
nonsequential cell references
One way ..
Put in L4: =INDIRECT("A"&ROWS($A$1:A1)*4) Copy down -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Tim" wrote in message ... In a worksheet I have got a set of data where column A contains a week ending date and then in columns B,C,D etc are the data refering to that week (so A4 is 7/10/04, B4 is 7/17/04 and so on). Elsewhere on the spreadsheet I want to copy every fourth row from the above-mentioned data set, so cell L4= cell A4, then L5= cell A8, L6= cell A12 and so on. Any ideas how to do it in a way that means I can easily drag a formula down, rather than what I have done so far which is to copy every cell and then gone through and deleted 3 weeks, miss a row and then delete the next 3 weeks data etc? Tim. |
#3
|
|||
|
|||
nonsequential cell references
Hi Tim
In cell L4 =INDIRECT("A"&(Row()-3)*4 Copy down as required Regards Roger Govier Tim wrote: In a worksheet I have got a set of data where column A contains a week ending date and then in columns B,C,D etc are the data refering to that week (so A4 is 7/10/04, B4 is 7/17/04 and so on). Elsewhere on the spreadsheet I want to copy every fourth row from the above-mentioned data set, so cell L4= cell A4, then L5= cell A8, L6= cell A12 and so on. Any ideas how to do it in a way that means I can easily drag a formula down, rather than what I have done so far which is to copy every cell and then gone through and deleted 3 weeks, miss a row and then delete the next 3 weeks data etc? Tim. |
#4
|
|||
|
|||
nonsequential cell references
Thanks for your help guys ... it is a beautiful thing you have come up with
.... and will save me loads of hassle. It works a treat when I drag the formula down. However, is it possible to modify it so that when I drag the formula to the right the relative column references also change? (my dataset is 10 columns wide) i.e. I need cell cell L4 to= A4 (and L5 to=A8 and so on down ... that problem you have already solved), but then I need to drag that across so that M4 =B4 and so on to the right. Can that be built into the indirect formula, or do I have to drag across and then manually change the "A" to B, C, D etc across the page? Thanks again for help so far, Tim. "Roger Govier" wrote: Hi Tim In cell L4 =INDIRECT("A"&(Row()-3)*4 Copy down as required Regards Roger Govier Tim wrote: In a worksheet I have got a set of data where column A contains a week ending date and then in columns B,C,D etc are the data refering to that week (so A4 is 7/10/04, B4 is 7/17/04 and so on). Elsewhere on the spreadsheet I want to copy every fourth row from the above-mentioned data set, so cell L4= cell A4, then L5= cell A8, L6= cell A12 and so on. Any ideas how to do it in a way that means I can easily drag a formula down, rather than what I have done so far which is to copy every cell and then gone through and deleted 3 weeks, miss a row and then delete the next 3 weeks data etc? Tim. |
#5
|
|||
|
|||
nonsequential cell references
Try...
L4, copied down and across: =INDEX(A$4:A$65536,ROWS(L$4:L4)*4-4+1) Hope this helps! In article , Tim wrote: Thanks for your help guys ... it is a beautiful thing you have come up with ... and will save me loads of hassle. It works a treat when I drag the formula down. However, is it possible to modify it so that when I drag the formula to the right the relative column references also change? (my dataset is 10 columns wide) i.e. I need cell cell L4 to= A4 (and L5 to=A8 and so on down ... that problem you have already solved), but then I need to drag that across so that M4 =B4 and so on to the right. Can that be built into the indirect formula, or do I have to drag across and then manually change the "A" to B, C, D etc across the page? Thanks again for help so far, Tim. "Roger Govier" wrote: Hi Tim In cell L4 =INDIRECT("A"&(Row()-3)*4 Copy down as required Regards Roger Govier Tim wrote: In a worksheet I have got a set of data where column A contains a week ending date and then in columns B,C,D etc are the data refering to that week (so A4 is 7/10/04, B4 is 7/17/04 and so on). Elsewhere on the spreadsheet I want to copy every fourth row from the above-mentioned data set, so cell L4= cell A4, then L5= cell A8, L6= cell A12 and so on. Any ideas how to do it in a way that means I can easily drag a formula down, rather than what I have done so far which is to copy every cell and then gone through and deleted 3 weeks, miss a row and then delete the next 3 weeks data etc? Tim. |
#6
|
|||
|
|||
nonsequential cell references
Domenic,
That's brilliant! Thanks for that ... it works (but I've no idea how: what is the Index formula telling Excel to do? If anyone can explain that I'd be even happier). Cheers, Tim. "Domenic" wrote: Try... L4, copied down and across: =INDEX(A$4:A$65536,ROWS(L$4:L4)*4-4+1) Hope this helps! In article , Tim wrote: Thanks for your help guys ... it is a beautiful thing you have come up with ... and will save me loads of hassle. It works a treat when I drag the formula down. However, is it possible to modify it so that when I drag the formula to the right the relative column references also change? (my dataset is 10 columns wide) i.e. I need cell cell L4 to= A4 (and L5 to=A8 and so on down ... that problem you have already solved), but then I need to drag that across so that M4 =B4 and so on to the right. Can that be built into the indirect formula, or do I have to drag across and then manually change the "A" to B, C, D etc across the page? Thanks again for help so far, Tim. "Roger Govier" wrote: Hi Tim In cell L4 =INDIRECT("A"&(Row()-3)*4 Copy down as required Regards Roger Govier Tim wrote: In a worksheet I have got a set of data where column A contains a week ending date and then in columns B,C,D etc are the data refering to that week (so A4 is 7/10/04, B4 is 7/17/04 and so on). Elsewhere on the spreadsheet I want to copy every fourth row from the above-mentioned data set, so cell L4= cell A4, then L5= cell A8, L6= cell A12 and so on. Any ideas how to do it in a way that means I can easily drag a formula down, rather than what I have done so far which is to copy every cell and then gone through and deleted 3 weeks, miss a row and then delete the next 3 weeks data etc? Tim. |
#7
|
|||
|
|||
nonsequential cell references
If we take a look at the following...
=INDEX(A$4:A$65536,ROWS(L$4:L4)*4-4+1) The ROWS function returns the number of rows within a specified range. In this example... ROWS(L$4:L4) --- returns 1 ROWS(L$4:L4)*4-4+1 --- also returns 1 This number is then used as an argument for the INDEX function and returns the value from the first cell of the specified range, A4:A65536. As the formula is copied/dragged to the next cell below, the formula becomes... =INDEX(A$4:A$65536,ROWS(L$4:L5)*4-4+1) Here... ROWS(L$4:L5) --- returns 2 ROWS(L$4:L5)*4-4+1 --- now returns 5 This time the value from the fifth cell of the specified range, A4:A65536, is returned, and so on... Hope this helps! In article , Tim wrote: Domenic, That's brilliant! Thanks for that ... it works (but I've no idea how: what is the Index formula telling Excel to do? If anyone can explain that I'd be even happier). Cheers, Tim. |
#8
|
|||
|
|||
nonsequential cell references
Brilliant. Thanks for taking the time to explain.
Tim. "Domenic" wrote: If we take a look at the following... =INDEX(A$4:A$65536,ROWS(L$4:L4)*4-4+1) The ROWS function returns the number of rows within a specified range. In this example... ROWS(L$4:L4) --- returns 1 ROWS(L$4:L4)*4-4+1 --- also returns 1 This number is then used as an argument for the INDEX function and returns the value from the first cell of the specified range, A4:A65536. As the formula is copied/dragged to the next cell below, the formula becomes... =INDEX(A$4:A$65536,ROWS(L$4:L5)*4-4+1) Here... ROWS(L$4:L5) --- returns 2 ROWS(L$4:L5)*4-4+1 --- now returns 5 This time the value from the fifth cell of the specified range, A4:A65536, is returned, and so on... Hope this helps! In article , Tim wrote: Domenic, That's brilliant! Thanks for that ... it works (but I've no idea how: what is the Index formula telling Excel to do? If anyone can explain that I'd be even happier). Cheers, Tim. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif function with remote cell references | Excel Worksheet Functions | |||
Fixed cell references | Excel Discussion (Misc queries) | |||
Cell references change when entering new data | New Users to Excel | |||
nonsequential cell references | Excel Worksheet Functions | |||
Absolute cell references and subsequent problems. | Excel Discussion (Misc queries) |