# Referencing Constants

#### nurikabe

##### New Member
In Excel 2000, is it possible to reference a constant (name) within a function using the value of anther cell? For example, how can I make the following work:

=A1*B1

where A1 is an integer and B1 is the name of a constant? Is there a character I can use to prefiex B1 such that Excel knows to look this up in its list of constants/names?

Thanks!

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Not sure this is what you want, but might want to investigate Evaluate method.

The more I think about it, are you sure that

Cells(Row,Col).Value * Cells(Row,Col).Value

wouldn't give you what you desire?

Have you tried your proposed technique in the Immediate window? How did it work?
This message was edited by stevebausch on 2002-09-23 20:38

Hi nurikabe:

You can assign the value of the constant to cell B1 by INSERT|NAME|DEFINE ...

and then in the formula use the assigned name rather than cell reference B1

see the simple worksheet simulation
Book2
ABCD
1\$20.006%
2costofgoodssoldMichigansalesTax
3
4\$1.20usingnamedconstantMIsalesTax
5
6\$1.20usingcellreferenceB1
7
Sheet2
</SPAN>

Regards!

Yogi
This message was edited by Yogi Anand on 2002-09-23 20:47

some egs:

using insert name | define, I set the name 'const1' as a constant (value = 100)
Book7
ABCD
110const1#VALUE!
2#REF!
31000
41000
5
Sheet2

Okay.. I'm starting to see the light. EVAL is what I want I think. Why, though, would I get a #NAME? error?

Aha! Got it. Thanks!

although one has to ask - if you're going to use eval(b1) where B1 contains the name of a constant, why not just have the value of the constant in B1??

I'm making this sheet for others to use. I want to define a list of currencies (USD, JPY, etc) and their exchange rates. This will allow users to enter the name of the currency rather than a constant exchange rate. For example:

200 USD
3000 JPY
40 GBP

The third column would be the formula =A1*EVAL(B1) where B1 is an exchange rate defined on another sheet.

A better solution to that might be to use a lookup table that has currencies in one column & (current) exchange rates in another, then use the appropriate vlookup() to return the correct exchange rates...

Replies
2
Views
125
Replies
0
Views
134
Replies
3
Views
195
Replies
3
Views
139
Replies
1
Views
206

1,196,044
Messages
6,013,066
Members
441,747
Latest member
darkman77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back