+ Reply to Thread
Results 1 to 6 of 6

Gantt Chart with hidden values

  1. #1
    Registered User
    Join Date
    06-06-2008
    Posts
    30

    Gantt Chart with hidden values

    I have made a Gantt chart to show the duration it take for each building to do an assessment. What I would like to do is to hide the buildings that do not have start dates so they will not show on the chart until they have a start date. For example, I don't want CO009, CO0631... to show on the chart.

    I want this automated. I can do it with a filter but I would have to go in and change it each time a start date is added.

    Sorry I can't add an example spreadsheet. It comes from classified data and I can't seem to recreate it on another workbook.

    Is this doable?


    Data.jpg

    Chart.jpg

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,555

    Re: Gantt Chart with hidden values

    Short but useless answer -- Yes, this is doable. The common technique I see is to use a dynamic named range (usually using the OFFSET() function) as the chart series data source. There are many tutorials online -- here's one: https://chandoo.org/wp/dynamic-chart-data-series/ Note that most examples have the empty cells at the bottom of the list, where your example has the empty cells at the top of the list. It will probably be easier to follow the tutorials if you will sort your data so that the empty cells in your data are also at the bottom of the list. Once you understand how the OFFSET() function works as a dynamic named range and how it applies in the chart, you can then adapt it to this scenario where the empty cells are at the top of the list -- if necessary.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,964

    Re: Gantt Chart with hidden values

    Can you not copy the workbook and anonymise the data the data then save and upload.

    This will probably need an event driven macro but we'll need a workbook if you want us to come up with a solution. No many of us are willing to recreate your data when you already have it in a workbook.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,964

    Re: Gantt Chart with hidden values

    ...and as a follow up to Mr Shorty, using Excel's standard filtering functionality to extract only the relevant records to another range and applying conditional formatting to that range would be another option.

  5. #5
    Registered User
    Join Date
    06-06-2008
    Posts
    30

    Re: Gantt Chart with hidden values

    I found a solution to manipulating the data showing by using an Dynamic feature with an Offset. I've got a couple more issues I'm not able to resolve.
    1. When I add a date (ex. CO0006) it does not re-rank them automatically.
    2. The chart does not resize dynamically so as the data shrinks the rows get bigger. I want the rows to remain the same size and have the chart resize as needed.

    I have a sample workbook attached. Any help would be appreciated.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,555

    Re: Gantt Chart with hidden values

    I could not replicate your problem number 1. As I edited the data in C2, the worksheet ranked and sorted it correctly in column 7 automatically. Check that you calculation setting is set to automatic. Otherwise, I have no suggestions.

    To my knowledge, Excel does not have a built in way to dynamically physically size a chart. You can write a macro in VBA that will:
    1) read the count of filled dates.
    2) decide how large to make the chart.
    3) then change the chart size using the height property of either the chartarea or plotarea objects (depending on exactly which object you want to change).

    To further automate it, associate the macro with an appropriate event (change or calculate or other event).

    How far down the VBA rabbit hole do you want to go?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Have the ability to view a daily Gantt chart as a monthly gantt chart
    By adam_d_john in forum Excel General
    Replies: 3
    Last Post: 03-13-2018, 09:11 PM
  2. Replies: 0
    Last Post: 01-07-2015, 01:22 PM
  3. Replies: 5
    Last Post: 10-04-2012, 07:01 AM
  4. Replies: 0
    Last Post: 07-13-2012, 06:40 PM
  5. Values from Hidden Column not showing in scatter chart
    By dshilan in forum Excel General
    Replies: 2
    Last Post: 06-17-2010, 10:46 AM
  6. Replies: 8
    Last Post: 12-09-2008, 12:38 PM
  7. Bars in Gantt chart showing values of certain cells?
    By darelooney in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2008, 10:10 AM

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