Using logicals in NAMES
Posted by Mike Galvin on September 25, 2001 10:59 AM
I have developed a worksheet that asks the user a series of questions and then, using a table of CONSTANTS, produces the results of various calculations using the answers to the questions and the table of constants. This works fine for one set of constants. Now, if I add a new set of constants for a different configuration (e.g. new version), i'd like to ask the user "what version he is using" and use that data to refer to a different Constant table.
My thought was that it would be helpful to use NAMES for each constant with a sub-reference logical (e.g. constant1_version). The logical could be a cell reference and when the user answers this version question, all formulas would point to the second set of constants.
I know I'm probably overlooking an easier way of doing this but I'm trying to avoid long formulas with multiple "if statements" and this method would make it easier for me to modify when a new version is released.
Any thoughts? TIA.
Mike
Posted by Aladin Akyure on September 25, 2001 11:15 AM
Mike --
Name the second set of constants e.g., CONSTANTS2, the first (original) CONSTANTS1 and modify your formulas such that they invoke the appropriate set depending on the user response.
If you want help on this, post a formula that uses the original set.
Aladin
Chan
Posted by Mike Galvin on September 25, 2001 11:39 AM
Aladin,
Thanks for the quick reply. In a way, what you have suggested is what I'm trying to accomplish but without editing the formulas every time a new version is released. Here is an example of one of my formulas:
=IF(C5="Single",mat_cost_single,mat_cost_dual)
I would like this formula to be rewritten to include a reference to the version. Something like this:
=IF(C5="Single",mat_cost_single_version,mat_cost_dual_version)
Using something like this, if the user inputted version 2.0, the formulas would know to look in the version 2.0 table for the mat_cost_single and mat_cost_dual constants.
Mike
Posted by Aladin Akyurek on September 25, 2001 12:23 PM
> Here is an example of one of my formulas:
This formula as such should produce a #VALUE! error. I observe this result, because I'm assuming that "mat_cost_single" is the name of a table (that is, a range of cells) and "mat_cost_dual"is the name of a different table. And these names can be seen thru Insert|Name|Define. Is my assumption is correct?
Or is "mat_cost_sigle" the name of a single cell containing a specific contant and "mat_cost_dual" the name of another single cell containing a different constant?
And, care to elaborate on C5?
Aladin
Posted by Richard S on September 25, 2001 4:46 PM
Lookups?
Just a thought, but if all the constants1 are in one column and all constants2 in the next next, couldn't you use a lookup function?
Richard
Posted by Mike Galvin on September 26, 2001 4:08 AM
Actually, mat_cost_single and mat_cost_dual are named cells, not ranges. Basically, the formula is asking if the value in cell C5 equals the text "Single" (user input field), then use the value in mat_cost_single, else use the value in mat_cost_dual.
YES
Posted by Mike Galvin on September 26, 2001 4:16 AM
Re: Lookups?
Richard, thanks. Yes I thought of that also. My table is not setup now in a single column but I'm thinking that this may be the way to go. Having some programming experience I am familiar with using variables for addressed and I thought Excel that might have had an equivilent method.
Thanks for the response.
Mike
Posted by Aladin Akyurek on September 26, 2001 8:13 AM
Mike --
The picture is now more clear.
Lets suppose that I have the following constants defined in sheet WS1:
A1 <== 1.34 (that is, A1 houses 1.34)
A2 <== 2.75
Suppose I name the cell A1 Constant_1 via the Name Box (on the Formula Bar and the cell A2 Constant_2.
Suppose also that the user selects or inputs a version number in say C1 in sheet WS2.
Suppose furhermore that I do a computation in D1, e.g.
the-value-in-E1 times constant-that-is-indicated-by-the-value-of-C1
I'd then enter the following in D1:
=E1*INDIRECT("Constant_"&C1)
I believe the foregoing is what you are looking for.
Aladin
==========
