Gantt Chart with hidden values

[shuddle]

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

[MrShorty]

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.

[Richard Buttrey]

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]

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

[shuddle]

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.

[MrShorty]

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?