Timeline showing location per person

[Eremit]

Dear all,

In an Access database the location per day and person is tracked, e.g.

Name Date Location
Joe 02.01.2015 Home
Joe 03.01.2015 Office
Joe 04.01.2015 Office
Joe 05.01.2015 School
Joe 06.01.2015 Office
Fred 02.01.2015 Office
Fred 03.01.2015 Office
Fred 04.01.2015 Office
Fred 05.01.2015 Office
Fred 06.01.2015 Client

I would like to get an overview in a timeline to know who is when where (see attachment). Any idea how this could be solved in Excel (or in Access). I would like most if not only the location would be shown by date, but have the fields for the same location combined (see attachment).

Where.png

In best case the starting point should also be variable, e.g. today.

Do you have any idea? Thanks for your responses.

Thanks

Eremit

[Fettertiger]

Hi Eremit,

as I am not sure of the structure of your file, i have created a new one and added the relevant macros inside.
TimeLine.xlsb
I tried to follow your colour coding as much as possible. You can easily adjusted it yoruself in the "Select Case" section of the macro if required.

Let me know if you need any help in adjusting the file to your needs. Pleae don't forget to mark the thread as solved, in case everythig works as required.

Theo

[Eremit]

Hi Theo,

Thanks a lot for your great solution. As I am not that familiar with VBA I will take some time trying to understand what it does.

Nevertheless it works great. The file does exactely, what I was looking for.

In this case I have a follow-up question. What would happen, if e.g. if the same day is having two locations (half day home, the other in the office). Could that be shown like Home/Office or in two lines (morning/afternoon)? In case that would be possible to integrate it further would need a selector for morning/afternoon. It could also be for location 1 and location 2, which would easily be added in the database.

Thanks

Eremit

[Fettertiger]

Hi Eremit,

rather than adding a column for location, you might want to add a column for am/pm. Add "am" if the location is in the mornings and "pm" if in the afternoon.

While adding a separator like a "/" could be done very easily, finding the correct colour would be very hard. To avoid this issue I have split mornings and afternoons into two columns in the Timeline sheet.

I understand your knowledge of VBA is limited, therefore I have added an extra "ColourCode sheet", where you can simply define the colour for each possible entry by formating the cell as required. The macro will pick up the color from there and apply it to the relevant cell. The beauty of this solution is that you can add different colours for "new" locations without having to access the Visual Basic Editor.

TimeLine2.xlsb

Let me know if it works as expected, and don't forget to press the "*" at the bottom left if you are satisfied with what I delivered

Theo

[Eremit]

Hi Theo,

Thanks for your updated solution. The look is good and the idea of implementing am and pm would be what I was looking for.

However I get a error message and the debugger pops up.

Do you have an idea?

Error.PNG

Thanks for your review.

BR

Eremit

[Fettertiger]

Hi Eremit,

your right, I forgot to dimension one variable.
TimeLine3.xlsb
Try this one please.

Theo

[Eremit]

Hi Theo

Many thanks for your great solution. It works perfectly.

Have a nice easter.

Eremit