Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dragging formula
Hi,
I have a problem while dragging the formula. Its better to give an example rather than explaining in the normal way. I have a formula =C7 in the cell B100 and =F7 in B101.. That means the row number remains the same (7) but the column number needs to be dragged in such a way that for each cell dragged down (from B100 to B110 and so on) every third column should be referred (i.e C7, F7, I7, L7 so on...) Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dragging formula
On Nov 14, 3:20 pm, Sasikiran
wrote: Hi, I have a problem while dragging the formula. Its better to give an example rather than explaining in the normal way. I have a formula =C7 in the cell B100 and =F7 in B101.. That means the row number remains the same (7) but the column number needs to be dragged in such a way that for each cell dragged down (from B100 to B110 and so on) every third column should be referred (i.e C7, F7, I7, L7 so on...) Thanks in advance =C$7 should work fine hth Carlo |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dragging formula
Hey Carlo,
Looks like my query was not clear... I have a formula =C7 in B100 while dragging it vertically its automatiically taking the next reference cell of the same column C (c7, c8, c9) Actullay I need the formula in this order C7, F7, I7, L7... so on Tried with =C$7 also but its not working... Kindly help.. "carlo" wrote: On Nov 14, 3:20 pm, Sasikiran wrote: Hi, I have a problem while dragging the formula. Its better to give an example rather than explaining in the normal way. I have a formula =C7 in the cell B100 and =F7 in B101.. That means the row number remains the same (7) but the column number needs to be dragged in such a way that for each cell dragged down (from B100 to B110 and so on) every third column should be referred (i.e C7, F7, I7, L7 so on...) Thanks in advance =C$7 should work fine hth Carlo |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dragging formula
=OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) and copy down
-- David Biddulph "Sasikiran" wrote in message ... Hi, I have a problem while dragging the formula. Its better to give an example rather than explaining in the normal way. I have a formula =C7 in the cell B100 and =F7 in B101.. That means the row number remains the same (7) but the column number needs to be dragged in such a way that for each cell dragged down (from B100 to B110 and so on) every third column should be referred (i.e C7, F7, I7, L7 so on...) Thanks in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dragging formula
Hello David...
Thanks a lot.. another query regarding the same.. Now I need to drag the formula =OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) horizontally to the next cell which should refer to C12 (thats *5 rows each time its dragged horizantally) and the row reference ROW(B100) in the above formula becomes C100 (that means the row reference will be *1 each time its dragged) Can a formula be put in B100 which can be dragged horizantally and vertically with the above specifications.. Thanks in advance... "David Biddulph" wrote: =OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) and copy down -- David Biddulph "Sasikiran" wrote in message ... Hi, I have a problem while dragging the formula. Its better to give an example rather than explaining in the normal way. I have a formula =C7 in the cell B100 and =F7 in B101.. That means the row number remains the same (7) but the column number needs to be dragged in such a way that for each cell dragged down (from B100 to B110 and so on) every third column should be referred (i.e C7, F7, I7, L7 so on...) Thanks in advance |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dragging formula
If you look at the syntax of the OFFSET and ROW functions (see Excel help),
you'll see how my formula works, so you'll be able to adjust it to your new requirements. -- David Biddulph "Sasikiran" wrote in message ... Hello David... Thanks a lot.. another query regarding the same.. Now I need to drag the formula =OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) horizontally to the next cell which should refer to C12 (thats *5 rows each time its dragged horizantally) and the row reference ROW(B100) in the above formula becomes C100 (that means the row reference will be *1 each time its dragged) Can a formula be put in B100 which can be dragged horizantally and vertically with the above specifications.. Thanks in advance... "David Biddulph" wrote: =OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) and copy down -- David Biddulph "Sasikiran" wrote in message ... Hi, I have a problem while dragging the formula. Its better to give an example rather than explaining in the normal way. I have a formula =C7 in the cell B100 and =F7 in B101.. That means the row number remains the same (7) but the column number needs to be dragged in such a way that for each cell dragged down (from B100 to B110 and so on) every third column should be referred (i.e C7, F7, I7, L7 so on...) Thanks in advance |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dragging formula
Try this formula *anywhere*, and drag across and down,
And see if it meets your specs: =INDEX($C$7:$IV$1000,5*COLUMNS($A:A)-4,3*ROWS($1:1)-2) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Sasikiran" wrote in message ... Hello David... Thanks a lot.. another query regarding the same.. Now I need to drag the formula =OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) horizontally to the next cell which should refer to C12 (thats *5 rows each time its dragged horizantally) and the row reference ROW(B100) in the above formula becomes C100 (that means the row reference will be *1 each time its dragged) Can a formula be put in B100 which can be dragged horizantally and vertically with the above specifications.. Thanks in advance... "David Biddulph" wrote: =OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) and copy down -- David Biddulph "Sasikiran" wrote in message ... Hi, I have a problem while dragging the formula. Its better to give an example rather than explaining in the normal way. I have a formula =C7 in the cell B100 and =F7 in B101.. That means the row number remains the same (7) but the column number needs to be dragged in such a way that for each cell dragged down (from B100 to B110 and so on) every third column should be referred (i.e C7, F7, I7, L7 so on...) Thanks in advance |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dragging formula
Fantastic Rag...
Thats really great.. Its working good.. If you can take some time of yours to understand me the formula that would be greatful.. Thanks and anticipating your reply "RagDyeR" wrote: Try this formula *anywhere*, and drag across and down, And see if it meets your specs: =INDEX($C$7:$IV$1000,5*COLUMNS($A:A)-4,3*ROWS($1:1)-2) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Sasikiran" wrote in message ... Hello David... Thanks a lot.. another query regarding the same.. Now I need to drag the formula =OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) horizontally to the next cell which should refer to C12 (thats *5 rows each time its dragged horizantally) and the row reference ROW(B100) in the above formula becomes C100 (that means the row reference will be *1 each time its dragged) Can a formula be put in B100 which can be dragged horizantally and vertically with the above specifications.. Thanks in advance... "David Biddulph" wrote: =OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) and copy down -- David Biddulph "Sasikiran" wrote in message ... Hi, I have a problem while dragging the formula. Its better to give an example rather than explaining in the normal way. I have a formula =C7 in the cell B100 and =F7 in B101.. That means the row number remains the same (7) but the column number needs to be dragged in such a way that for each cell dragged down (from B100 to B110 and so on) every third column should be referred (i.e C7, F7, I7, L7 so on...) Thanks in advance |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dragging formula
You're welcome, and thank you for the feed-back.
As to explaining the workings of the formula: Index() is a function that has 2 forms. It can either return a value or it can return a reference. In this case we're using it to return values. Put simply, you can index a range, and then refer to a cell in that range. Your starting point is C7, and I arbitrarily chose the last Column (IV) as the ending column at Row1000. The first argument in Index() is the range, the 2nd is the row, and the 3rd is the Column. So, =Index(C7:IV1000,1,1) would return the contents of C7, since *everything* starts at the *start* of the indicated range. Index(C7:IV1000,2,1) would return the contents of C8, while Index(C7:IV1000,1,2) would return the contents of D7. What we therefore need to do, is figure a way to increment the 2nd and 3rd arguments, so that the rows and columns will change in the appropriate series that we need, as we copy the formula. The 2 best functions for this are Columns() and Rows(). In any cell, try: =Columns(A:A) Copy across a few columns and down a few rows. You'll see that the return is "1", and it doesn't change with the copying. Change it to: =Columns($A:A) And do the same, and you'll see that it increments when copying across, but *does not* increment as you copy down. This is because you anchored the function at "A" ( 1 ), while allowing the second column reference to change. Do the same test with the Rows() function, =Rows(1:1) and you'll see that the same thing happens, except it'll increment when copied down and not when copied across. So you can see how this would be useful in a formula that you want to copy in 2 directions, with different references changing dependent on the direction of the copy. Therefore, you should now understand how: =Index(C7:IV1000,Columns($A:A),Rows($1:1)) Would return the contents of C7. Don't be confused ... as stated above ... the 2nd Index() argument determines *ROWS*, We're using the Columns() function in the 2nd argument because we want the ROWS to increment as we copy *across*, And the 3rd argument (Columns), to increment as we copy down. The rest of the formula is simply a mathematical calculation to enable the row and column references to follow the numerical series we need as they are copied down and across. Take: =5*Columns($A:A)-4 And copy across and down, and take note of the returns. Do the same with: =3*ROWS($1:1)-2 After you understand the workings of Index(), the difficulty in completing the formula is simply figuring out the mathematics to attain the numerical sequence you're looking for. -- Regards, RD ---------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------------- "Sasikiran" wrote in message ... Fantastic Rag... Thats really great.. Its working good.. If you can take some time of yours to understand me the formula that would be greatful.. Thanks and anticipating your reply "RagDyeR" wrote: Try this formula *anywhere*, and drag across and down, And see if it meets your specs: =INDEX($C$7:$IV$1000,5*COLUMNS($A:A)-4,3*ROWS($1:1)-2) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Sasikiran" wrote in message ... Hello David... Thanks a lot.. another query regarding the same.. Now I need to drag the formula =OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) horizontally to the next cell which should refer to C12 (thats *5 rows each time its dragged horizantally) and the row reference ROW(B100) in the above formula becomes C100 (that means the row reference will be *1 each time its dragged) Can a formula be put in B100 which can be dragged horizantally and vertically with the above specifications.. Thanks in advance... "David Biddulph" wrote: =OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) and copy down -- David Biddulph "Sasikiran" wrote in message ... Hi, I have a problem while dragging the formula. Its better to give an example rather than explaining in the normal way. I have a formula =C7 in the cell B100 and =F7 in B101.. That means the row number remains the same (7) but the column number needs to be dragged in such a way that for each cell dragged down (from B100 to B110 and so on) every third column should be referred (i.e C7, F7, I7, L7 so on...) Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dragging a formula containing external links | Excel Worksheet Functions | |||
Dragging a formula correctly? | Excel Worksheet Functions | |||
dragging a formula down a column | Excel Worksheet Functions | |||
when dragging formula how do i avoid #DIV/0! | Excel Discussion (Misc queries) | |||
dragging a formula | Excel Discussion (Misc queries) |