• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dragging down in excel

P

pr4peace

New Member
Hi All,


This might be a super stupid question.

When I drag down a cell with a formula , the non$ items in the cell increment in the same direction as the reference one row at a time, but when the dragging down needs to refer data which are not one cell down , but instead 4 cells down from each other , how do we tell excel to drag down like that.

I tried entering two formulas with the corresponding cell differences and then dragging but excel still only increments by 1 row down.


Also, after selecting the cell and if we double click on the bottom right, it fills the formula vertically, is there a way to do this horizontally?


Thanks in Advance.


Prashanth
 
You will need to include the logic to do that yourself

This will typically involve an Offset and Row functions
 
Hi Prashanth ,


This is how Excel is supposed to work ; all references are taken in relation to the active cell. Suppose in cell B1 , you have a formula =IF(F17>0,G11+H11,L13+K13) ; the way Excel represents this formula is clear , if in the Excel options , you switch to the R1C1 reference style. You will now see in the formula bar , the following :


=IF(R[16]C[4]>0,R[10]C[5]+R[10]C[6],R[12]C[10]+R[12]C[9])


Now , if you copy this same formula one cell down , to C1 , you will see :


=IF(R[16]C[4]>0,R[10]C[5]+R[10]C[6],R[12]C[10]+R[12]C[9])


The two formulae are identical ! Even though the active cell has moved one row down , with reference to the active cell , nothing has changed ! Which is how it should be , since otherwise , you could never copy a formula from one cell to another.


If you can post the formula that you wish to copy , the solution can be given.


Narayan
 
:)


We are back to the DPR sheet.

There is no complicated formula.


In Derived Sheet

Say R1C1: = 'Scope&Actuals'!S6

Say R2C1: = 'Scope&Actuals'!S10

Say R3C1: = 'Scope&Actuals'!S14 and so on....


The increment is exactly 4. The source sheet will not change.


So when I drag down from R1C1 & R2C1, what excel does is

Say R1C1: = 'Scope&Actuals'!S6

Say R2C1: = 'Scope&Actuals'!S10

Say R3C1: = 'Scope&Actuals'!S8

etc...


Thanks in Advance !!

Prashanth
 
Hi Prashanth ,


Can you clear some confusion ?


Are you saying that in a sheet if you have the following formulae in cells A1 and A2 :


='Some other sheet'!S6


='Some other sheet'!S10


then when you drag this down to A3 and onwards , the formulae should change as shown below :


A3 : ='Some other sheet'!S14


A4 : ='Some other sheet'!S18


A5 : ='Some other sheet'!S22


and so on ?


Narayan
 
Hi Hui,


Appreciate the help. But with the limited knowledge of excel I possess I am not able to decipher the logic in the formula, hence I am not able to use it to my problem.


Can you please help me in a more NOOB way.


Thanks,

Prashanth
 
Hi Prashanth ,


ROW() returns the number of the row in which the formula is present ; if this formula is in A3 , ROW() will be 3 ; in A4 , ROW() will be 4 and so on.


Multiplying this by 4 and adding 2 results in values of 6 , 10 , 14 , 18 , 22 ,... starting from 6 for row 1 , 10 for row 2 ,...


Since OFFSET works with an initial value of 0 , you subtract 1 from the above result.


Narayan
 
Hi,


Thank you so much.

But my problem is the formula does not start in the first line as there are some headings etc. In my sheet the first formula is in row 3 which should correspond to the row 6 in the data sheet.


row 3 - > row 6

row 4 -> row 10

row 5 -> row 14

row 6-> row 18


I guess the first formula has to be hardcoded as there is no logic, but row 4 onwards can it be automated?


I need to be brush up my simultaneous equations :)


Bye

Prashanth
 
Yipeee found it!!!


=> row()*4 -6 :)


Super thanks to Hui and Narayan for the formula and all the help.


Bye

Prashanth
 
Hi, pr4peace!


Just taking a walk, I didn't analyze all previous posts but for the last one I think that this modification will work:


From first column to second: ROW()*4-6

From second column to first: (ROW()+6)/4


Hope it helps you.


Regards!
 
Hi, pr4peace!

It seems as if 23 hours ago both or us wrote the same. I didn't notice when I first wrote.

Glad you find it out. And the other way went for free, I was just there... it was one more line. :)

Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top