• 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.

Autofill Formulas Horizontally with cell number increment

holdsworth

New Member
Before I start I understand that this problem has occured due to bad design but the amount of time put into the form so far prevents me from turning back!


I currently have a formula as basic as =$a1-$b1, I want to be able to drag this right and get =$a2-$b2 etc etc, I have over 600 rows to do with 12 columns so doing a paste drag down copy paste transpose over 600 times isnt a great solution, any help would be great!
 

aires

New Member
Holdsworth,


You can do it with offset().


Create a line over the line you want the answer with increasing numbers (1,2,3...) (starting on C1, for instance).


And let's presume the series you want to subtract start at A2 and B2. So, insert the following formula on C2:


=OFFSET($A$1;C$1;0)-OFFSET($B$1;C$1;0)


If you drag it to the right, it creates the desired result.


Hope that helps.


Best regards, Aires.
 
Top