Chart showing date and names on a time axis

Hi guys,

I'm trying to create a chart/matrix where you should be able to see a list of names on the y-axis and a time line (dates) on the x-axis. Each name (y-axis) should have a "marker" along the x-axis aligned with the name, where a date is displayed on the side of the marker.

The whole idea is to create a chart (or similar) where you can easily see when you have last contacted/communicated with a certain person.

I've attached a "mock up" of how I suggest it can look, but I've had no luck what so ever in creating this on my own.

Anyone got any bright ideas?

Hi Andy!
Thanks ever so much! This looks really good!

Is it also possible to add a generic line (the red line in my mock up) that shows where "today" is in the chart?

Hi again,

If I want to add more names and dates to the chart, how do I do that? I've managed to add more names and the bar, but the blue circle and date box do not appear when adding more names.

Hi again for the 3rd time

In the attached file I would like to add a few more things if possible.
1. A vertical timeline that represents today's date
2. Another circle (other color) that shows a new (later) date (next communications date)

Any suggestions?

Thanks ever so much Andy! Really good work! This will be very helpful for me.

One last questions though. If I change (extend) the x-axis maximum value to one more year (2014-01-01), the "event bar" for some reason extends beyond the blue event point. I can't seem to change this so it looks correct. Any thoughts?

Cheers!

Ah, my mistake. It's working now.

Thanks a million! You're a star!

Hi again!

Yet another issue ha arisen. Now I am trying to link the chart to the actual dates on the "Stakeholders Sheet". The dates works fine, but when I filter it all looks very strange. I think it's got to do with the Y-pos list on the "Data sheet" but I can't find a way to solve so it.

So I want the chart to be generic and be able to only show the names that are included after filtering (like the first chart (Stakeholder Matrix)).
Is this possible?

Cheers!

Hi,
Ok, so it's not possible to have everything on the stakeholder sheet so that I don't need to filter on two places?

Hi, and once again thank you for your help.

When I filter it reduces the number of blue circles to the same number of names which is very good. However, they don't align in the chart which makes it hard to read. It seems as it has something to do with the second x-axis maximum values. It looks good (aligned) if I chose the fixed maximum value to be the same as the number of names. Is it possible to make the maximum value to automatically update?

Cheers!

Fantastic! I must have missed enabling the macros. Now it works fine!

BTW, do you know if it's possible to have the colors of the bubbles in the first chart (Stakeholder Matrix) to automatically update to be the same as the "Support" column on the Stakeholder sheet?

So for instance, if name 13 is a "blocker" (red) the circle next to the name in the chart should also be red. At the moment I've only changed the colors manually. But it would be gold if that could be done automatically.

In any case, thanks a million for your genius help!

Cheers!

Thanks!

How do I add more rows to both charts? Say I want to always have the possibility to add 50 names to the list that should also display in the charts. I tried just copying the rows but I get a "data array" error and I can't seem to add anymore rows into the charts.

Any ideas?

Hmm, I really wish for once I could reply and say, "No probs Andy! I got it" ...Unfortunately my skills are apparently equal to an onion.

When I try to amend and extending the formula I get the message "You can not change part of an array". I've tried different ways of changing it but no luck. Has it got anything to do with that some of the rows are hidden? Why are they btw?

Cheers!

Hi,

Ok, with your guidance I got the range to work and "bubbles" to appear in the charts. On the first chart however I can't for my life get the bubbles to display the correct data label, Name 1-30. Instead it's showing just a number value (x or y value).
What am I missing?

Also, when I filter (as you can see in the attachment, some rows get an invalid reference (column AJ & AK). Why is that?

Cheers!

Hi, I think the formulas are correct now. However something still seems to be wrong though. For instance, "Name 18"(column B) is on the same row showing "Name 30" in column AK. And the rows below are just showing "NUM!

I'm afraid I don't understand how to do with the labels. Terribly sorry.

KR