Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

Fill formulas with row changing not column numbers

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
bretthonn13
Asked:
bretthonn13
1 Solution
 
javaftperCommented:
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
 
bretthonn13Author Commented:
Yes, but when I include the '$' and do a fill down, it fills down the exact formula.  The letters do not increment.
0
 
bretthonn13Author Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SteveCommented:
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
 
byundtCommented:
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
 
bretthonn13Author Commented:
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
 
byundtCommented:
Here it is in Excel 2013. The trick was the $ before the row numbers. It won't work if you don't do that.
Formula copied acrossPasteSpecial dialog with Transpose box checkedFormulas transposed with incrementing letters
0
 
Saqib Husain, SyedEngineerCommented:
That formula works just fine and I never knew that it would work that way.

Good work Brad.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now