Creating a drop down list with a functionality depending on choice

Question

I want to create a drop down list (e.g. list of currencies, EUR;USD;CHF) such that whenever one currency is chosen, it will create a plot (using the data for that currency) for that chosen currency. Do I have to write a code in VBA?(I dont know how to write such code) Any help is really appreciated.

Answer 1

An alternative would be to have:

 - one cell containing the drop down where the users makes the selection (e.g. in D5)

 - one cell containing the applicable exchange rate, depending on the selection made in the dropdown.  This cell would contain a formula like:  =VLOOKUP($D$5,$P$2:$Q$5,2,FALSE)

 - a range of cells (P2:Q5) that contains the currencies ("USD", "EUR", etc.) in col P and the exchange rate to your base currency in col Q.  If you're home currency is Euros, the applicable rate for EUR would be 1.

Now you have a cell containing the required rate to be used in all your calculations.  You would simply multiple the current price in your home currency (e.g. Euros) by the exchange rate contained in $D$5.

Make sense?

Cheers

Rich

Answer 2

You don't need code.  You can use Data Validation.

1. Write your list of currencies in some cells, let's say Sheet2!A1:A4 contains USD, GBP, EUR and CHF.

If you have your list on a different sheet to the dropdown, you have to use a Named Range.  Select Sheet2!A1:A4 then do Formula tab > Defined Names group > Define Name.  Enter "Cur" for the Name, then click OK.

2. Select the cell where you want the dropdown to appear, let's say Sheet1!D5.

Do: Data tab > Data Tools group > Data Validation.  Set the Allow field to List, and enter   =Cur   in the Source field.  OK.

You should now have your dropdown containing the currencies in your list.

Now you need to set up your data for plotting.  To do this you should set up your data so you have one column that <could> be plotted for each currency.  So let's say you have your X values in column J, Y values for USD in column K, Y values for GBP in column L, Y values for EUR in column M and Y values for CHF in column N.

Cells J1:N1 should contain your currencies (USD, GBP, EUR and CHF).  Now you can use HLOOKUP to return another column for the selected currency.  So in cell P1 enter   =HLOOKUP($D$5,$K$1:$N1,ROWS(K$1:N1),FALSE)

$D$5 is the cell where the dropdown is.  Note the two $ signs.

$K$1:$N1 is the currency columns, down to the current row. 

ROWS(K$1:N1) counts the current row number relative to the top of your table range.

FALSE makes it look for an exact match between D5 and the first row of the table (K1:N1).

You can now plot column P against your X values, and your chart will change when you make a selection from the dropdown in D5.

There are many other ways to do things like this, it all depends on what calculations you are doing depending on which selection is made....

Hope that helps.

Cheers

Rich

Answer 3

Sorry, I don't  understand...

You have 4 columns, one for each currency...

You have a dropdown where you want to select a particular currency...

How will the plot change depending on the selection from the dropdown list????

Cheers

Rich

Answer 4

Thanks Rich, but there is one more point left, that i would like to ask.. Again on same example, assuming for each currencies, i have 4 columns of data..And i want to plot all these 4 columns according to choice from dropdown list..

 The way you mentioned just allow me to draw one column for each currencies..

Thanks in advance,

Sercan

Answer 5

Thanks a lot..That really helped..