Referencing Constants

nurikabe

New Member
Joined
Sep 22, 2002
Messages
10
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0
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
 
Upvote 0
Okay.. I'm starting to see the light. EVAL is what I want I think. Why, though, would I get a #NAME? error?
 
Upvote 0
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??

paddy
 
Upvote 0
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.
 
Upvote 0
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...

Paddy
 
Upvote 0

Forum statistics

Threads
1,223,309
Messages
6,171,340
Members
452,396
Latest member
ajl_ahmed

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top