Copying Formula

N

Neil_Pattison

I have a spreadsheet for calculating the quantity of materials require
for a job. It starts with a text column for a description of th
material then a calculated column for quantities and then a number o
columns for different work procedures.

Row Number 1 is the names of the procedures and the calulations star
from row 2. Basically in Row 2 the user enters how many procedures ar
to be carried out and it is then calculated to show the quantit
required for each material.

The problem I have is that when I go to copy the formula (to calculat
the quantity) down through all the required cells it doesn't cop
through as I need it to. [eg of formula i
=(A2*J12)+(B2*K12)+(C2*L12)+(D2*M12)+(E2*N12)+(F2*O12)+ etc]

When I go to pull this formula down through the other cells in eac
subsequent it changes t
[=(A3*J13)+(B3*K13)+(C3*L13)+(D3*M13)+(E3*N13)+(F3*O13)+ etc] where a
I need it to stay as (A2*K13)+(B2*K13)+(C2*L13) and so on.

Is there a way where I can copy through all the cells but keep th
formula as (A2*???)+(B2*???) etc

I need to be able to copy them down so that new rows can be added int
the middle of the worksheet and already have the formula in there.

Any help on this would be greatly appreciated. Thank
 
B

Bryan Hessey

Neil,

Precede a row or column reference with $ (dollar) to prevent the
increment, thus use ($A$2*???)+($B$2*???) where applicable.


Neil_Pattison said:
I have a spreadsheet for calculating the quantity of materials required
for a job. It starts with a text column for a description of the
material then a calculated column for quantities and then a number of
columns for different work procedures.

Row Number 1 is the names of the procedures and the calulations start
from row 2. Basically in Row 2 the user enters how many procedures are
to be carried out and it is then calculated to show the quantity
required for each material.

The problem I have is that when I go to copy the formula (to calculate
the quantity) down through all the required cells it doesn't copy
through as I need it to. [eg of formula is
=(A2*J12)+(B2*K12)+(C2*L12)+(D2*M12)+(E2*N12)+(F2*O12)+ etc]

When I go to pull this formula down through the other cells in each
subsequent it changes to
[=(A3*J13)+(B3*K13)+(C3*L13)+(D3*M13)+(E3*N13)+(F3*O13)+ etc] where as
I need it to stay as (A2*K13)+(B2*K13)+(C2*L13) and so on.

Is there a way where I can copy through all the cells but keep the
formula as (A2*???)+(B2*???) etc

I need to be able to copy them down so that new rows can be added into
the middle of the worksheet and already have the formula in there.

Any help on this would be greatly appreciated. Thanks
 
N

Neil_Pattison

Thanks bryan the problem with the formula is sorted now. Another problem
I have is when inserting a new row into the middle of the worksheet I
want the formula to automatically copy into the new row. How do I get
this to happen?
 
B

Bryan Hessey

Neil,

Assuming that you don't want to get into VB code and the setting up of
buttons,
when you insert a new row, go to the row above, and, highlight all
cells that are formula, (hold the CTRL key and select all formula
cells), then drag the formula down a row (via drag the small square in
the bottom right of the highlight).

Hope this helps
 
N

Neil_Pattison

The problem I have with that is that I have protected the cells
containing the formula so that a user cannot mistakenly delete it, so
they won't be able to select the cell and then drag it down
 
B

Bryan Hessey

Neil.

You can elect for the user to be able to select locked cells, and to
Insert rows, this should help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top