constructing names in excel (not in VB!)

Posted by Carl Wells on September 24, 2001 3:33 AM

Hi,
I need to be able to construct a name so that I can use it in a formula. What I mean by construct a name is, using a simple example, imagine a cell, "EUR". I need to be able to read this string from a cell and combine it with "DOLLAR_" and "_FX_RATE" to give the name (already defined elsewhere in the workbook) "DOLLAR_EUR_FX_RATE" for use in a formula
e.g. =5*DOLLAR_EUR_FX_RATE.

In my problem, the variable is the cell containing "EUR", which thus creates other names e.g. "DOLLAR_CHF_FX_RATE"

another thing I'm going to need to do is create calls to bloomberg along these lines (the ticker used will vary and want to replace that part of the string with a reference to a cell containing the ticker).

Any help much appreciated, I don't want to use VB for this because I want auto updating if I change a cell, and VB would be more work and less transparent (my boss doesn't trust macros either ;) ).

Thanks in advance,

Carl

Posted by Ian on September 24, 2001 5:37 AM

You could use the Indirect Function:

=5*INDIRECT("DOLLAR_"&E1&"_FX_RATE")

Where E1 can contain the middle part for the named range

If you EUR in E1 and name A1 DOLLAR_EUR_FX_RATE
(or where ever you have that Named Range), this will return the value in A1 (or wherever)

You could put IF(E1,INDIRECT("DOLLAR_"&E1&"_FX_RATE")*B1,"")

which would A: not give an error if E1 contains nothing and B: multiply the Named Range by the figure you put in B1 (in your case 5)

Any help??

Ian

Posted by Ian on September 24, 2001 5:43 AM

Sorry an error....Corrected

The second part of my formula returns a value error (exactly what I said it wouldn't do)

use

=IF(ISBLANK(E18),"",5*INDIRECT("DOLLAR_"&E18&"_FX_RATE"))

Instead

Ian

Posted by Carl Wells on September 24, 2001 6:32 AM

Re: Sorry an error....Corrected

Posted by Carl Wells on September 24, 2001 6:33 AM

Re: Sorry an error....Corrected

Thanks awfully,
I've just realised from your answer that I asked a very similar question about 6 weeks ago, but thank you very much for refreshing my memory, you have saved me hours of work! (not to mention reducing my current stress level significantly :) )

Carl