# Dragging Formulas to Adjacent Cells

1. ## 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

2. ## 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. ## 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. ## 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?

5. ## Re: Dragging Formulas to Adjacent Cells

Marvin,

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

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

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

#### 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