Is this an efficient use of named cells?
 

Hi,

Let's say I want to be able to use one of 3 different configurations in
a sheet. The configuration in use is controlled by a combo box. One of
the things that is determined by the configuration is a discount rate.

The way I have defined this is as follows:
I specify each rate in a separate cell, and name the cells rate_1,
rate_2 and rate_3.
I have another cell that contains the rate in use, named rate_in_use.
In the code for the combo box change event, I have the following:

Code:
--------------------
If configuration = 1 Then
ActiveWorkbook.Names("rate_in_use").RefersTo = ActiveWorkbook.Names("rate_1").RefersTo
Else etc.
--------------------

All of the cells that need this discount rate can now refer to the cell
named rate_in_use.

I have repeated this technique for several other cells that are
dependent on which configuration is selected.

Is this an efficient way of doing what I want to do, and are there any
alternatives? The one thing that I wouldn't want to change is the use
of a combo box to choose the configuration.

(note that the code above could be improved by just appending the
configuration number to the end of the name, but I didn't show it here
so as to make the example easier)

Thanks,
Vindaloo


--
Vindaloo
------------------------------------------------------------------------
Vindaloo's Profile: http://www.excelforum.com/member.php...o&userid=32634
View this thread: http://www.excelforum.com/showthread...hreadid=526100

Is this an efficient use of named cells?
 

Looks like a workable approach to me.

--
Regards,
Tom Ogilvy


"Vindaloo" wrote:


Hi,

Let's say I want to be able to use one of 3 different configurations in
a sheet. The configuration in use is controlled by a combo box. One of
the things that is determined by the configuration is a discount rate.

The way I have defined this is as follows:
I specify each rate in a separate cell, and name the cells rate_1,
rate_2 and rate_3.
I have another cell that contains the rate in use, named rate_in_use.
In the code for the combo box change event, I have the following:

Code:
--------------------
If configuration = 1 Then
ActiveWorkbook.Names("rate_in_use").RefersTo = ActiveWorkbook.Names("rate_1").RefersTo
Else etc.
--------------------

All of the cells that need this discount rate can now refer to the cell
named rate_in_use.

I have repeated this technique for several other cells that are
dependent on which configuration is selected.

Is this an efficient way of doing what I want to do, and are there any
alternatives? The one thing that I wouldn't want to change is the use
of a combo box to choose the configuration.

(note that the code above could be improved by just appending the
configuration number to the end of the name, but I didn't show it here
so as to make the example easier)

Thanks,
Vindaloo


--
Vindaloo
------------------------------------------------------------------------
Vindaloo's Profile: http://www.excelforum.com/member.php...o&userid=32634
View this thread: http://www.excelforum.com/showthread...hreadid=526100