Help with a food inventory formula

[solcan]

Hi all, first post here.

I have a formula:

=L4*E5+M4*E6+N4*E7+O4*E8+P4*E9+Q4*E10+R4*E11+S4*E12+T4*E13+V4*E15+W4*E16+X4*E17+Y4*E18+AA4*E20+AB4*E21+AC4*E22+AD4*E23+AF4*E25+AG4*E26+AH4*E27+AI4*E28+AK4*E30+AL4*E31+AM4*E32+AN4*E33+AP4*E35+AQ4*E36+AR4*E37+AS4*E38+AT4*E39+AV4*E41+AW4*E42+AX4*E43+AY4*E44+AZ4*E45+BA4*E46+BB4*E47+BD4*E49+BE4*E50+BF4*E51+BG4*E52+BH4*E53+BI4*E54+BK4*E56+BL4*E57+BM4*E58+BN4*E59+BO4*E60+BP4*E61+BQ4*E62+BR4*E63+BT4*E65+BU4*E66+BV4*E67+BW4*E68+BX4*E69+BY4*E70+BZ4*E71+CB4*E73+CC4*E74+CD4*E75+CE4*E76+CF4*E77+CG4*E78+CI4*E80+CJ4*E81+CK4*E82+CL4*E83+CM4*E84

What I want to do is copy this formula into a whole lot of different cells by dragging with the mouse. My problem is that I want the Horizontal (letters) to change but the series of E's must stay the same. IE, the beginning of the second cell i'm putting the formula into would start:

=L5*E5+M5*E6+N5*E7.....

Is there a quick way to do this or do i need to manually edit the formula for each individual cell?



p.s. this is for a food stock system with the menu items running horizontally and the ingredients running vertically. The series of 'E' blocks is the sales for the month. The other variable (Row 4, Row 5, Row 6 etc) will have a number in it corresponding to the amount of each ingredient in each menu item. The formula I'm trying to put in place is the total stock items used for each individual ingredient. The idea is obviously to then have a detailed print out each month of what items were used for sales as a double check on the inventory count.

 

[Domski]

Hi,

Have a look at the Excel help for Relative/Absolute/Mixed References. It should help you out.

Your formula would end up like this: =L5*$E5+M5*$E6+N5*$E7...

Dom

 

[solcan]

Thanks very much, worked like a charm!