quickly repeating long-winded formula

fairkatrina

New Member
Joined
Jun 3, 2010
Messages
4
please bear with me with this...

I'm trying to write a spreadsheet for stocktaking where rows 2-40 have the stock items, column C has the price, and then each subsequent column has a different job I've used the stock for, so I can go down the rows adding how many of which item I've used for each job. I want to be able to calculate at the bottom of each column from D onwards the total spend on that individual job, and I've ended up with a very longwinded formula in each cell that goes something like =SUM(D2*C2)+(D3*C3)...and so on until D40!

While I can live with this, to replicate the formula in each column I can only think of copying and pasting it into the next cell in the row, all the way along the row, but each time I do this excel moves all the cells in the formula down one column, which obviously works for the column relating to the specific job (D, E, F etc) but I need column C to stay fixed throughout all the formulas. At the minute the only way I can solve this is to manually change the formula back to column C by dragging the highlighted cells individually, which means I'm repeating this 39x for each column.

Is there *any* way of either making the formula simpler, or being able to drag all 39 highlighted cells that are in the wrong column across to C at once?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

sulakvea

Well-known Member
Joined
Jul 2, 2008
Messages
994
sorry didnt read the whole post (a very annoying coworker is trying to talk to me)... seems like you need SUMPRODUCT(Range1, Range2)
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
You can add a $ sign in front of any part of any cell reference that you don't want to change as you copy the cell.

A1 = everything is relative, the column and row references will change
$A1 = Column is absolute, row is relative
A$1 = Column is relative, row is absolute
$A$1 = everything is locked as absolute
 

Forum statistics

Threads
1,144,630
Messages
5,725,394
Members
422,623
Latest member
Dave52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top