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