+ Reply to Thread
Results 1 to 6 of 6

Conditional Graph Source Data

  1. #1
    Registered User
    Join Date
    10-13-2008
    Location
    Australia
    MS-Off Ver
    2007
    Posts
    85

    Conditional Graph Source Data

    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
    Last edited by benno87; 09-07-2009 at 02:35 AM. Reason: rewording

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,176

    Re: Conditional Graph Source Data

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

    What makes you say the formula approach is infeasible?
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    10-13-2008
    Location
    Australia
    MS-Off Ver
    2007
    Posts
    85

    Re: Conditional Graph Source Data

    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...
    Attached Images Attached Images

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,176

    Re: Conditional Graph Source Data

    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

  5. #5
    Registered User
    Join Date
    10-13-2008
    Location
    Australia
    MS-Off Ver
    2007
    Posts
    85

    Re: Conditional Graph Source Data

    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

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,176

    Re: Conditional Graph Source Data

    there are plenty of examples on the forum of setting the XValues and Values properties of a chart.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1