Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 63
Default 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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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.



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 32
Default 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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
cell currently being evaluated contains a constant LucyMR Excel Discussion (Misc queries) 3 May 8th 23 03:44 AM
creating a "constant" number to be used in formulas tkg Excel Discussion (Misc queries) 3 February 14th 07 01:33 AM
How to keep formulas constant [email protected] Excel Discussion (Misc queries) 4 June 29th 06 08:04 AM
Constant cell Aurora Excel Worksheet Functions 1 February 14th 06 04:08 PM
the cell currently being evaluated contains a constant Barrie Wells Excel Worksheet Functions 2 June 2nd 05 12:59 PM


All times are GMT +1. The time now is 09:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"