Anomandaris

05-29-2009, 04:49 AM

Hi guys,

Xld helped me get this code yesterday, I'm trying to modify it slightly.

2 things.

1. The calculation of the spread is slow. It worked really well for the data set I provided, but my actual datasets are 64 columns by 500+ rows each...so excel freezes for abt 3-5 mins while running it. Is there another way to calculate faster?

2. Xld(im hoping you're looking at it as you're familiar with this now), in the workbook you solved previously there were 4 markets UK,US, JPN, EUR on different sheets, and each sheet had 5 datasets- for A,B,C,D,E. I've chnaged that a bit now to suit my real sheet as I cant fit many 64 by 500 matrices in one page and its too much clutter.

So now each page has 2 datasets one Fixed range and one variable .. Alternative A (is fixed dataset) remains the same....the rest (variable dataset) i will call ' 1m, 3m, 6m, 1y' but only one will be displayed and that will depend on a cell in that worksheet...say cell B3.

1m = 21

3m = 63

6m = 126

1y = 252

So when I make my selection on sheet 'chart' lets say US 1m v EUR 3m, then macro will insert '21' into cell B3 of the sheet 'ROPE' containing US data, and insert '63' into B3 of sheet with EUR data which is 'HOOD'....see when this value is inserted, it will change the dataset, i have formulae set up on the whole range which is dependent on this cell 'B3'.

is something like this possible? my concern was that we are getting 4 different selections into one visible range...

Or is it better to have 2 options 'Fixed' and 'Variable' for selecting a named range ...and if 'Variable is selected then to choose from a set of option buttons where we can select whether its 1m,3m,6m,1y...and all this will do is insert the value into cell B3?

So thats all, this reduces visible data sets from 20 to 8.....

i hope i was clear, if not please let me know and i'll clarify

thanks

Xld helped me get this code yesterday, I'm trying to modify it slightly.

2 things.

1. The calculation of the spread is slow. It worked really well for the data set I provided, but my actual datasets are 64 columns by 500+ rows each...so excel freezes for abt 3-5 mins while running it. Is there another way to calculate faster?

2. Xld(im hoping you're looking at it as you're familiar with this now), in the workbook you solved previously there were 4 markets UK,US, JPN, EUR on different sheets, and each sheet had 5 datasets- for A,B,C,D,E. I've chnaged that a bit now to suit my real sheet as I cant fit many 64 by 500 matrices in one page and its too much clutter.

So now each page has 2 datasets one Fixed range and one variable .. Alternative A (is fixed dataset) remains the same....the rest (variable dataset) i will call ' 1m, 3m, 6m, 1y' but only one will be displayed and that will depend on a cell in that worksheet...say cell B3.

1m = 21

3m = 63

6m = 126

1y = 252

So when I make my selection on sheet 'chart' lets say US 1m v EUR 3m, then macro will insert '21' into cell B3 of the sheet 'ROPE' containing US data, and insert '63' into B3 of sheet with EUR data which is 'HOOD'....see when this value is inserted, it will change the dataset, i have formulae set up on the whole range which is dependent on this cell 'B3'.

is something like this possible? my concern was that we are getting 4 different selections into one visible range...

Or is it better to have 2 options 'Fixed' and 'Variable' for selecting a named range ...and if 'Variable is selected then to choose from a set of option buttons where we can select whether its 1m,3m,6m,1y...and all this will do is insert the value into cell B3?

So thats all, this reduces visible data sets from 20 to 8.....

i hope i was clear, if not please let me know and i'll clarify

thanks