Thread: Creating chart, conditional on cell clicked

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