Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Copying Formula
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 -- Neil_Pattison ------------------------------------------------------------------------ Neil_Pattison's Profile: http://www.excelforum.com/member.php...o&userid=27696 View this thread: http://www.excelforum.com/showthread...hreadid=472528 |
#2
|
|||
|
|||
Neil, Precede a row or column reference with $ (dollar) to prevent the increment, thus use ($A$2*???)+($B$2*???) where applicable. Neil_Pattison Wrote: 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 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=472528 |
#3
|
|||
|
|||
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? -- Neil_Pattison ------------------------------------------------------------------------ Neil_Pattison's Profile: http://www.excelforum.com/member.php...o&userid=27696 View this thread: http://www.excelforum.com/showthread...hreadid=472528 |
#4
|
|||
|
|||
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 Neil_Pattison Wrote: 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? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=472528 |
#5
|
|||
|
|||
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 -- Neil_Pattison ------------------------------------------------------------------------ Neil_Pattison's Profile: http://www.excelforum.com/member.php...o&userid=27696 View this thread: http://www.excelforum.com/showthread...hreadid=472528 |
#6
|
|||
|
|||
Neil. You can elect for the user to be able to select locked cells, and to Insert rows, this should help. Neil_Pattison Wrote: 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 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=472528 |
#7
|
|||
|
|||
Thanks for your help bryan -- Neil_Pattison ------------------------------------------------------------------------ Neil_Pattison's Profile: http://www.excelforum.com/member.php...o&userid=27696 View this thread: http://www.excelforum.com/showthread...hreadid=472528 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding row to forumla | Excel Discussion (Misc queries) | |||
Copying a Formula To Reference Multiple Worksheets | Excel Worksheet Functions | |||
Copying an Array Formula | Excel Discussion (Misc queries) | |||
Copying Formula Text | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions |