Is there a way to set a cell value to be constant in formulas?
Using Excel 2007.
I am fairly new to Excel and need some assistance. I am wanting to create a spreadsheet with one cell that has a constant value that will be used to calculate a total with other cells. Example: A1 times (constant value cell A7) A2 times (constant value cell A7) A3 times (constant value cell A7) A4 times (constant value cell A7) What I am having to do is change the "constant value cell" location every time I copy the original cell that has the formula. Example: First cell = A1*A7. If I copy this cell to A2, the fomula will change to A2*F8. I then have to change the F8 back to the "constant value cell" location F7. My question is this. Is there a way to, for the lack of a better way to describe it, lock the "constant value cell" location in a formula so I don't have to change it every time I copy the formula to a different cell. I hope this explains my situation. Thanks for any help. |
Is there a way to set a cell value to be constant in formulas?
Yes, there is a way...
Make the reference to cell F7 absolute: put $ before row and column indication, eg $F$7. "Marshall" schreef in bericht ... Using Excel 2007. I am fairly new to Excel and need some assistance. I am wanting to create a spreadsheet with one cell that has a constant value that will be used to calculate a total with other cells. Example: A1 times (constant value cell A7) A2 times (constant value cell A7) A3 times (constant value cell A7) A4 times (constant value cell A7) What I am having to do is change the "constant value cell" location every time I copy the original cell that has the formula. Example: First cell = A1*A7. If I copy this cell to A2, the fomula will change to A2*F8. I then have to change the F8 back to the "constant value cell" location F7. My question is this. Is there a way to, for the lack of a better way to describe it, lock the "constant value cell" location in a formula so I don't have to change it every time I copy the formula to a different cell. I hope this explains my situation. Thanks for any help. |
Is there a way to set a cell value to be constant in formulas?
Hi,
For your constant cell use an absolute reference =A1*$A$7 that way when the formula is dragged the reference will stay the same. Mike "Marshall" wrote: Using Excel 2007. I am fairly new to Excel and need some assistance. I am wanting to create a spreadsheet with one cell that has a constant value that will be used to calculate a total with other cells. Example: A1 times (constant value cell A7) A2 times (constant value cell A7) A3 times (constant value cell A7) A4 times (constant value cell A7) What I am having to do is change the "constant value cell" location every time I copy the original cell that has the formula. Example: First cell = A1*A7. If I copy this cell to A2, the fomula will change to A2*F8. I then have to change the F8 back to the "constant value cell" location F7. My question is this. Is there a way to, for the lack of a better way to describe it, lock the "constant value cell" location in a formula so I don't have to change it every time I copy the formula to a different cell. I hope this explains my situation. Thanks for any help. |
Is there a way to set a cell value to be constant in formulas?
You need to make the cell reference into absolute instead of relative.
To do this you add a $ to either the column or row part (or both) to fix them so they do not change when you copy a formula elsewhere. So, your first example would become: =A1*$A$7 Pressing F4 whilst in edit mode allows you to cycle through the various settings. Hope this helps. Pete On Nov 2, 9:28 am, Marshall wrote: Using Excel 2007. I am fairly new to Excel and need some assistance. I am wanting to create a spreadsheet with one cell that has a constant value that will be used to calculate a total with other cells. Example: A1 times (constant value cell A7) A2 times (constant value cell A7) A3 times (constant value cell A7) A4 times (constant value cell A7) What I am having to do is change the "constant value cell" location every time I copy the original cell that has the formula. Example: First cell = A1*A7. If I copy this cell to A2, the fomula will change to A2*F8. I then have to change the F8 back to the "constant value cell" location F7. My question is this. Is there a way to, for the lack of a better way to describe it, lock the "constant value cell" location in a formula so I don't have to change it every time I copy the formula to a different cell. I hope this explains my situation. Thanks for any help. |
Is there a way to set a cell value to be constant in formulas?
putting a $ sign makes it absolute.
A1*$A$7. "Marshall" wrote in message ... Using Excel 2007. I am fairly new to Excel and need some assistance. I am wanting to create a spreadsheet with one cell that has a constant value that will be used to calculate a total with other cells. Example: A1 times (constant value cell A7) A2 times (constant value cell A7) A3 times (constant value cell A7) A4 times (constant value cell A7) What I am having to do is change the "constant value cell" location every time I copy the original cell that has the formula. Example: First cell = A1*A7. If I copy this cell to A2, the fomula will change to A2*F8. I then have to change the F8 back to the "constant value cell" location F7. My question is this. Is there a way to, for the lack of a better way to describe it, lock the "constant value cell" location in a formula so I don't have to change it every time I copy the formula to a different cell. I hope this explains my situation. Thanks for any help. |
All times are GMT +1. The time now is 01:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com