Solved

# Fill formulas with row changing not column numbers

Posted on 2013-05-24
346 Views
I have a formula that needs to be filled down.  I want the cell letter to increment and not the number.  I guess I want the row to increase while the column stays static.

Original formula:  =SUM(C4, C12, C20)

I want to fill down for several rows:  =SUM(D4, D12, D20), =SUM(E4,E12,E20),  ETC...
0
Question by:bretthonn13

LVL 4

Expert Comment

ID: 39194753
This is called relative positioning.  In excel you have 4 options-

A1 - row and column increment
\$A1 - row only increment
A\$1 - column only increment - YOUR ONE
\$A\$1 - fixed/absolute

so you need-
=SUM(C\$4,C\$12, C\$20)
0

Author Comment

ID: 39194772
Yes, but when I include the '\$' and do a fill down, it fills down the exact formula.  The letters do not increment.
0

Author Comment

ID: 39194782
Original formula:  =SUM(C\$4,C\$12, C\$20)

want several rows below it like this:

=SUM(D\$4,D\$12,D\$20)
=SUM(E\$4,E\$12,E\$20)
etc
0

LVL 24

Accepted Solution

Steve earned 500 total points
ID: 39194787
Something like:
=OFFSET(\$D\$4,0,ROW()-1)+OFFSET(\$D\$12,0,ROW()-1)+OFFSET(\$D\$20,0,ROW()-1)

Will work if in Row 1 and copied down
Example.xlsx
0

LVL 80

Expert Comment

ID: 39195134
If you want an easy to read formula when you are done, try the following manual technique:

1. Put the following formula to the right of any data, then copy across as required:
=SUM(C\$4, C\$12, C\$20)
2. Copy the cells containing that formula
3. Select the cell where the first formula needs to go
4. Paste Special using the Transpose option. You'll need to click the Paste Special... at bottom of the dialog to see the checkbox for Transpose at bottom right of next dialog.
0

Author Comment

ID: 39195167
byundt - that way never seems to increment anything for me.  I've read that solution before but can't seem to get it to work.  I guess I would need to see some screenshots of that in action.
0

LVL 80

Expert Comment

ID: 39195210
Here it is in Excel 2013. The trick was the \$ before the row numbers. It won't work if you don't do that.
0

LVL 43

Expert Comment

ID: 39195214
That formula works just fine and I never knew that it would work that way.

0

## Featured Post

### Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

#### Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!