Formulas & Contants

[angelitos]

Dear Friend

Using excel do you know what do I
have to do to put a constant in a
cell as a result of a formula?

Thank you
angelitos

 

[Dreamboat]

You must copy it. Do not move the cursor. Then hit Edit-Paste special-Values, Ok.

You can do this with groups of cells or individually.

http://www.TheWordExpert.com

 

[angelitos]

Dear DreamBoat

I get your answer, but my question is: Using a formula how can I put the result in other sell like a constant automactly?

Thank you
angelitos

http://www.angelitos.tv

 

[Dreamboat]

I don't think it's possible; at least not without VBA.

http://www.TheWordExpert.com

 

[DaleWatson123321]

angelitos,

If I understand you correctly, I think all you need to do is to:

1) Go to the cell location where you want the "constant".

2) Enter a simple formula that refers to the original cell. If the original formula is in cell "A1", for example, then enter this formula =A1. To make it "constant" (or "absolute&quot, you need to enter a the "$" character before the column letter and before the row number - i.e. =$A$1.

An "easy" method of referencing another cell - in an "absolute" way, is by first assigning a RANGE NAME to the cell. Then in your other cell, simply type =costs - where "costs" is the name you assigned to the original cell.

To assign a Range Name, used the following steps:
1) Highlight the cell.
2) Hold down the <Control> key and hit the <F3> key.
3) Type the name.
4) Hit <Enter>

Caution: Don't create Range Names that conflict with Excel's cell coordinates or with numbers. For example, don't create a name such as &quot;C14&quot; - instead use &quot;_C14&quot;.

If you have another case where you want to assign a Range Name to a range-of-cells, as opposed to one cell, this is also possible and you would use the same Range-Naming procedure. Then, for your formula, you could use another function such as =SUM(costs). Whenever a formula contains a reference to a Range Name, and the formula is copied to another location, the reference to the named range will stay “fixed” or “absolute”.

Hope this helps.

Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

P.S.

While use of the Range Names is often a preferred method, I should have also mentioned the following as an optional means of making a cell reference &quot;absolute&quot;...

After you've typed the cell reference, hit the <F4> key. You'll see that it places the $ character before the column and row.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[angelitos]

Dear Deal

I get your advice but I'm talking about to tranform
the result of the formula in a constant. I'm not
talking about make a cell constant, I'm talking
about make the result of a formula in a constant
y another cell.

Thank you!
angelitos

http://www.angelitos.tv

 

[DaleWatson123321]

angelitos,

Then Dreamboat is RIGHT - &quot;AGAIN&quot; !!! ...you DO need to use VBA.

Here's a VBA routine that will do the job...

Sub Create_Constants()
Range(&quot;constant_1&quot.Value = Range(&quot;formula_1&quot.Value
End Sub

This requires that you create Range Names for the names used in the routine - i.e. for &quot;constant_1&quot; and &quot;formula_1&quot;.

This routine will convert the formula to a &quot;value&quot; (or a constant).

Using this method, you can have your formulas on one sheet, and your &quot;constants&quot; on any other sheet.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca