View Full Version : linking ranges into dropdown and option boxes

05-25-2009, 04:32 AM
Hi guys,
I need some advice on how i should organize this spreadsheet....
So on the main page it will have a set of option boxes and dropdowns to select data and then press a button to run a macro that
produces a chart...I've got the graphing code figured out for one set of data, but I'm confused about how to link all the other data.
You may see the attached spreadsheet to get an idea of what the main page would look like.
Data selection-
At the top there are 2 dropdown boxes, where you can either put one market against the other (eg US vs UK) to get the difference/spread, or you can set US vs US, EUR vs EUR to simply analyse data from that single market.
So 1st step is to select a market - US, UK, JPN, or EUR
Then again there are 2 dropdowns.
Then you have to select one of 5 alternatives .
A,B,C,D,E ----so there are 5 ranges of data for each of the markets making it a total of 20 ranges (is it easier to name the ranges and refer to them using the name?)
So if you have US - Alternative C and EUR -Alternative D
then the following option box should have these options:
1-US ALternative C (just analyses US alternative C data)
2.EUR alternative D (this option just analyses EUR alternative D data range)
3. US alternative C vs EUR alternative D (this is the spread, the US data - EUR data )

So I'd have to select one of these options from the box
then next step is a charting criteria
2nd set of option boxes(select 1)
-standard deviation

at this point i'm done --I should click a button to produce a chart.

The yellowboxes are the dropdowns, i dont know why i wasnt able to find the option to create dropdown boxes. And FYI the ranges are set by date, i've shown a typical one on sheet 2...So when doing spreads it would be the difference on a certain date, and if the cell was a blank then we would ignore that date and that observation.

I'm not sure how these dropdowns and option boxes can be linked along with the data, any suggestions?

05-25-2009, 07:41 AM
I have read that twice, and have no idea what you are asking, how those selections on the first sheet relate to the data on the second, why you couldn't find a combobox (there are two types), and so on.

05-25-2009, 12:19 PM
hi xld,

oh sorry i wasnt clear, that data set is only for one of the ranges(lets say US-alternative C).....there's 20 of these ranges.

And i found the combo boxes, thanks, for some reason i thought it was called dropdown box and was searching for that (haha bonehead move)

I'll show you a more detailed file shortly which i anticipate will clarify this a bit

05-26-2009, 03:19 AM
slightly more info on this.....see the sheet 'Raw', there will be a few of those sheets with Data(which is imported into excel), here we can see one Range of Data(US Alt-C)
So on the sheet 'Chart' we make our selection, lets say US Alt-C vs EUR alt-D....(EUR Alt D will exist in another sheet -similar range) so what i need to happen is to subtract EUR from US and show the values in the sheet 'Datafor chart', here once the data is in place it will calculate
a few statistics on the top of the sheet(current,avg, std dev, and z-score)..........this is then displayed in the chart(i have the macro to do this last part -take stats from Table 1 and graph it)

i hope you sort of get a picture of what im trying to do, i just need to link these data sets to a combo box and option buttons in that selection sheet 'Chart'.
is it better to do this with macro or excel formula?

the file is too big, unfortunately i cant upload a larger version of it