Creating chart, conditional on cell clicked

Hi there,

Well, you were right - it was a bit complicated - not too difficult to program, but not all that easy to understand!

Please take a look at the attached and let me know if I've understood your requirements correctly. The only thing the user has to do is to enter the required dates and then select the required values of Term and Product from the dropdown lists. The graphs will update automatically.

I'd be glad to have your feedback on this one, even if it's only to let me know that I've completely misunderstood what you had in mind!

Regards,

Greg M

sorry, i know its not easy to understand as i've got 'Years/ time' on both axes...but you guys got it right.
Excellent work, both of you Greg and Andy! Looks like you guys put some time in for this, definitely does the job and looks organised.
But do you know by any chance if the dates could be selected with a scrollbar, just that I was asked to do it with scrollbars for ease of use.

thanks a lot guys...both versions work

Hi again,

I originally thought about using dropdown lists for the dates, and doing this will avoid the possibility of entering a date for which no data are available. However, the possible disadvantage is that users may have to scroll down through several hundred dates on the list, and this might prove to be more trouble than it's worth.

It's not a problem to make the dates available from a dropdown list, but are you sure that's the best approach?

Incidentally, if you're interested, I produced a slightly modified version of the workbook which would allow you to change the layout of your main data worksheet, i.e. to have more/fewer columns before the date column, and more/fewer rows before the title column.

Please let me know if you want me to implement the date dropdown list or anything else.

Regards,

Greg M

thanks guys, i'll just try plugging in a scrollbar, i've tried before and failed i'll give it another shot

Hi again,

Take a look at the attached workbook and see if the scrollbar does what you want.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi again,

Sorry - try this version instead! It will cope with up to 1000 dates.

Please let me know how you get on.

Regards,

Greg M

thanks Greg, the scroll bar does work like i wanted, now I'll try plugging it into my sheet, hopefully i can make it work there like you did, i'm surprised there was no vba required at all for the whole charting process

Hi again,

Thanks for your feedback - I'm glad I was able to help.

Ok on not needing VBA for the chart updating. I usually find that it's easier to "point" a chart at a specific range and to manipulate the contents of that range, than to try to manipulate the chart itself. As a consequence, the amount of VBA required is often very little, and relates just to updating one or two cells whose values are then used to populate the data range .

Regards,

Greg M

I'll mark this solved now, sorry about the delay was travelling last few days.
You guys did a fantastic job hee, thanks....though I just cant get my scrollbar to work, I'm making some very basic mistake I'm sure, but that's ok. If I run in to any problems while making additions to the model I'll let you know

thanks again

Hi Greg,

here's the scrollbar issue, when I tried to link it to the cell 'ptrScrollOffset1' the scrollbar disappeared...

Oh and another thing, about labelling the chart -- on the current version for Term 1y, see that on the X-axis we have 1y in 1y, 1y in 2y, 1y in 5y etc... is there a way to make that 1y,2y,5y, and so on till 30y on the X-axis, and at the top of the chart it'll say Term 1y.....

something like that, would make it look more concise, whats the formula for that, do i type it into Chart Title after selecting chart options?


thanks again buddy

Hi again,

Ok - I think things started to go wrong for you when you decided to use the Activex scrollbar (from the Control Toolbox toolbar) rather than the one you find on the Forms toolbar. Activex controls are more powerful, but also less user-friendly.

I've adjusted the x-axis titles on the attached version and hope that I've got them the way you wanted.

Hope this helps - please let me know how you get on.

Regards,

Greg M

thanks Greg..

i didnt know about the forms scrollbar before, it is way easier for sure, but
something's wrong I'm getting dates from Jan 2000 to Sep 2002 which is not even in my range so its giving me a blank graph. Am I supposed to hihlight the Dates Range in 'Data' while making thescrollbar?

Hi again,

I've included the latest version with only minor modifications which you probably won't even notice.

Ok, I'll try to talk you through the way Scrollbar_1 (the one in Cell M8 of the "Graphs" worksheet) works.

The Cell Link property of Scrollbar_1 is the named cell "ptrScrollOffset1" (H47) of the "Graph data" sheet, so the value in this cell will increase/decrease in response to clicking on Scrollbar_1.

So what happens next? We need to scroll down through the dates in Column B of the "Data" sheet, beginning at "StartCell" (Cell B16), and pick the Nth date cell, where N corresponds to the value stored in Cell H47.

The named cell "ptrDateStartCell" (D47) on the "Graph Data" sheet contains the address of "StartCell", i.e. '[Heat Map - 6.xls]Data'!$B$16. This address is calculated AUTOMATICALLY from the values stored in the named cells "ptrSourceSheet" (K29), "ptrSourceColumn" (K30) and "ptrSourceRow" (K31) - the workbook name is entered automatically by the Cell("Address", ....) formula in Cell D47.

The formula in the named cell "ptrScrollDate1" (J47) uses the address of "StartCell" as its reference point and the value of "ptrScrollOffset1" (H47) as its offset to pick a date from the list. This date is simply repeated in Cell C30. If the offset selects a date beyond the end of the list (i.e. beyond Cell B528) , the Cells J47 and C30 will display "End of Range", and the formula in the named cell "ptrNoData1" (E30) will display "No Data Available".

You shouldn't have to modify or select anything in the workbook in order to use it. The sheets are protected, and the only unlocked cells in the workbook are Cells H47 & H48 on the "Graph Data" sheet, which are accessed by the scrollbar controls. In addition, Cells K29, K30 & K31 on this sheet are unlocked in order to cater for any alterations to the name or layout of the "Data" sheet.

I hope this explanation helps - please let me know if there's anything else you need.

Regards,

Greg M

I realized my mistake, it was with the defined names, I had messed up with one of them, they all seemed so similar, silly error haha
thanks a bunch dude, you really put in a lot of time for this
you should be charging money!

Must say its a cool learning experience for me too...there's just so much excel can do..

Hi again,

Many thanks for your feedback. I'm glad to hear that things are working correctly at your end, and pleased that I was able to help.

You're right - Excel is one incredible application!

Regards,

Greg M