Pick a name from a list for use in a formula

[junglehat]

Hi,

Could someone help with this.

I have a formula that needs to produce a result based on the selection of a forex instrument from a list.

e.g. I am using GBPEUR AUDUSD, AUDTWO as the names for the instruments.

each of these has a scaling factor such as 10000, 100 or 10

I have set up a drop down list to choose the instrument. Now I want to refer to the selected instrument as a value that can be used in the formula.

GBPEUR is a name that has a value of 10000
AUDUSD is a name that has a value of 100
AUDTWO is a name that has a value of 10

the formula would be:

=IF(F53="Long",sum(J53-I53),sum(I53-J53))*(GBPEUR)
or
=IF(F53="Long",sum(J53-I53),sum(I53-J53))*(AUDUSD)
or
=IF(F53="Long",sum(J53-I53),sum(I53-J53))*(AUDTWO)

when I try it the selection evaluates as text and not as a name.

Any help would be much appreciated.

Bob

[Tony Valko]

One way...

Create this 2 column table:

GBPEUR........10000
AUDUSD.......100
AUDTWO......10

Assume that table is in the range G2:H4.

A1 = drop down list

Then:

=IF(F53="Long",J53-I53,I53-J53)*VLOOKUP(A1,G2:H4,2,0)

[Palmetto]

Another option that doesn't require the lookup table and is suitable for short lists

=IF(F53="Long",J53-I53,I53-J53)*LOOKUP(A1,{"AUDTWO","AUDUSD","GBPEUR"},{10,100,1000})

[junglehat]

Many thanks for the excellent advice,

Tony Valko's solution was perfect and also taught me about VLOOKUP.

Bob

[Tony Valko]

Good deal. Thanks for the feedback!