+ Reply to Thread
Results 1 to 6 of 6

Dragging Formulas to Adjacent Cells

  1. #1
    Registered User
    Join Date
    10-28-2008
    Location
    chicago
    Posts
    6

    Dragging Formulas to Adjacent Cells

    I have some data in a spreadsheet that I'm trying to manipulate and I'm not sure if what I want to do is possible.

    I have data in B2: D2 and I have data in E2:E4. So one set of data goes across the columns and the other goes down the rows. I want to subtract these two sets of data. I click on a new cell (B21) and put =B2-E2. That works fine. However when I drag the formula to populate the cells is where my problem starts.

    I want to drag the formula across the columns to populate B21:P21. The problem is that when I drag across the columns B2 increments to C2, D2 etc...I would like it to instead increment to B3, B4 etc...

    Is is possible to drag a formula in one direction (whether across the page or down the page) and have the cells used in the formula increment in the opposite direction?

    I hope that was explained well enough for someone to figure out what I mean. I attached a file as well so you might be able to see what I'm talking about. Thanks
    Attached Files Attached Files
    Last edited by skratchmo; 04-27-2011 at 11:38 PM.

  2. #2
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Rowley
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: Dragging Formulas to Adjacent Cells

    Not sure this is exactly what your looking for but try this in cell B21:

    =INDIRECT("B"&COLUMN())-INDIRECT("E"&COLUMN())

    The COLUMN() function will return 2 for column B, 3 for Column C, etc, etc.

  3. #3
    Registered User
    Join Date
    10-28-2008
    Location
    chicago
    Posts
    6

    Re: Dragging Formulas to Adjacent Cells

    xenixman,

    thanks for the reply but that's not what I was talking about.

    I'm trying to do this:

    in the attached file i want to put the formula =B2-$E$2 into cell B21. I then want to drag the formula from B21 to P21. However when I drag the formula across the cells from left to right it increments B2 from left to right as well. This means B2 then becomes C2 then becomes D2 etc...(i.e. when I drag the formula from cell B21 to cell C21 the formula now becomes =C2-$E$2). This is not what I want to do.

    I would like to drag the formula in cell B21, which is =B2-$E$2, from left to right and have B2 increment to B3 then to B4 etc... By this I mean when I drag the formula to cell C21 the formula should be =B3-$E$2.

    So I would like to drag the formula from left to right while having the variable increment down (B2 to B3 to B4 and so on instead of incrementing B2 to C2 to D2 etc...

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Dragging Formulas to Adjacent Cells

    Hey,

    I think you do want the Indirect function suggested above or a close answer to it.
    Try
    Please Login or Register  to view this content.
    in B21 and pull to the right. Is that what you want?
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    10-28-2008
    Location
    chicago
    Posts
    6

    Re: Dragging Formulas to Adjacent Cells

    Marvin,

    I'll try it out again...maybe I was just using it wrong.

  6. #6
    Registered User
    Join Date
    10-28-2008
    Location
    chicago
    Posts
    6

    Re: Dragging Formulas to Adjacent Cells

    I just tried it again exactly how you wrote the code (and how xenixman wrote it too) and it worked.

    When I tried it before I was trying to put an argument in the column() function and it wasn't doing what I wanted.

    Anyway thanks for the help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1