Conditional Graph Source Data

**benno87** · 09-07-2009, 02:34 AM

Hi all,

I have a chart that I would like to change the source data of. The source data would be dependant on a set of values within a data validation field. Then when I click a "Refresh" button, the chart will be updated with a set of values depending on what is selected in the data validation.

I've thought about having the values in one set of cells and using a formula for these cells, derived from the data validation but this just simply isn't feasible. Is there some VB code that can change the source data of a particular chart?

Hope this explains my dilema, thanks for your help.

Cheers,

Benno

**Andy Pope** · 09-07-2009, 04:43 AM

Without more information and or an example workbook it's hard to say.

What makes you say the formula approach is infeasible?

**benno87** · 09-07-2009, 08:48 PM

Hi,

Thanks for your reply Andy. I can't create a mock up book as it is a bit too much data to modify. But I have attached a jpg of what the report looks like. I want to be able to filter the graph by sales, last year sales, budget, and receipts, all of which have a different field. I can't create a formula to change the respective data because I want all the data to be visible at all times.

Unless I create a new set of formulas in hidden cells, or something of the like but I'd rather not create unecessary formulas. There is already a macro running (the 'Refresh' button) so if I could add some code into that which would update the chart depending on what is selected in a data validation list cell (yet to be created), this would be a lot simpler to use & create.

Your thoughts...

**Andy Pope** · 09-08-2009, 03:48 AM

Can you not create a set of chart data in row 43 that indexes the previous rows based on information that tells it will row to use.

So if you pick Actual Sales it will use the values from row 31.
If you want Budget is will use row 41

**benno87** · 09-21-2009, 12:53 AM

Hi,

Sorry for my delayed reply. I could do this however I would prefer to do this through VBA, as there is already VBA in use. Could you please let me know a way to do this in VBA. Surely there is a way, especially considering VBA does everything...

Thanks for your help!!

Cheers