+ Reply to Thread
Results 1 to 2 of 2

Excel VBA Time Series Graph

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Excel 2010

    Excel VBA Time Series Graph


    Very new to vba (well been 5 years) so please advise if there is anything missing or confusing.

    I am trying to create a time series graph (bar graph) which illustrates a timeline of when projects will be started. I am able to create this graph by the engineer placing the year and quarter into a column which will then be graphed accordingly. The next phase of this spreadsheet is for the engineer to place a status into a column which will automatically change then data point colour to a predefined colour.

    For example, table.

    Column A | Column B | Column C | Column D | Column E | Column F | Column G |
    Row 1: Task Name | Year | Quarter | Start Date| End Date | Length | Status |
    Row 2: Server 2012| 2013 | 3 | 1/07/2013 | 30/12/2013 | 91 | Review (fill colour blue) |
    Row 3: lync 2012| 2013 | 3 | 1/07/2013 | 30/12/2013 | 91 | complete (fill colour green)|
    Row 4: exchange 2012| 2013 | 3 | 1/07/2013 | 30/12/2013 | 91 | review (fill colour blue) |

    The approach i have taken to date is to code a vba macro which looks for predefined text in a column and if matches then set cell fill colour, which works. I then want to graph this data point to be set to the same fill colour. To date all i am able to do is change the fill colour of the cell which does not cross over to the graph.

    For example the graph and each data point would be coloured as such
    Review: blue
    approved: purple
    Rejected: Red
    Complete: Green

    I have attached my xsl file to help.

    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10

    Re: Excel VBA Time Series Graph

    I have done this before, but I am a little sketchy on remembering how to do it.

    Baiscally, if I remember correctly, what you need to is to add a series for each of them, Network Servicing Series, Network Box Upgrade series etc. in the chart.

    Then you need to have a summary table for each of your headers which uses an IF statement to only select the relevant data, so the rest of the series is 0.

    When you plot this, there will be a colour for each series by default, which you can then change.

    So if you have 4 categories, you want a summary table with 4 rows, then the chart is plot off the summary table, giving 4 columns of differing colours.

    I am a bit busy today, if I get chance i'll show you.

    Also, all of this doesn't really need to be done by code. Using Specific Text - Contains in the conditional formatting should do the first bit, and a chart off a summary table wont need code either, just setting up in line with the conditional formatting.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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


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