Automatically generating custom timeline on separate tab (Excel 2010)

[jsb13]

Hi all,

I'm trying to get Excel 2010 to automatically generate a custom timeline in a separate tab using data I enter in a different tab. In the attached example, the data in the first tab ("daily log") tracks the number of trucks parked at a certain yard by the state they are registered in, their company, and their ID number. I would like Excel to automatically generate the timeline I've created by hand in the second tab ("Timeline"). I need a formula that returns a "1" if several conditions are met (see comments in example) and a " " if those conditions are not met. Also, I need the formula to return special characters (i.e. 1~ or 1^ based on the location of activity and the types of op11043erations that occur, see example comments for clarification). I've got an demonstration example of everything I need in the attachment. Tab two has the data from tab 1 manually entered in the way I need it.

The formula should be flexible enough to allow new types of data to be incorporated into the timeline, and needs to be something I can copy into other cells.

I played around with IF(AND...) functions, but I've never used them before and couldn't make them work. There are a few complicating factors: there aren't a consistent number of trucks at the yard per day, and some trucks changed their ID numbers during the data period. I use a blank row, highlighted in dark grey, to visually differentiate days. This row can be filtered out with the "null" column. Any help you could provide would be most appreciated!

Please let me know if anything in the example is not clear, or if you would like any additional information.

Thanks!

[jsb13]

Don't know why, but the attachment inserted itself in the middle of the post, first paragraph.

[SamT]

Post # 2 first: You had the Inline box (at cursor) checked when you uploaded.

Formulas are not flexible in the way you want. They require specific references.

Your timeline sheet will grow vertically for every truck that enters a yard and horizontally every day that passes. What limits on this do you envision?

You are wanting to display many different Data Points in a single cell. Possible, but hard to read and each Data Point must have (a fairly long) part of any formula that returns them. Formulas get exponentially harder to understand and maintain, the longer they are.

Virginia trucks get special treatment on the Timeline ???

You are currently only dealing with 4 states. Is it possible that that will change?

On the Timeline, MD and VA are one color and IL and WI are another. What is the significance?

Please read the quote in my signature. You have definately shown us how you are trying to accomplish what you want, but if we knew what you want, we might know a better way.

With what little I can imagine, I'm thinking that you might have to use VBA procedures.

[SamT]

You are wanting to display many different Data Points in a single cell. Possible, but hard to read and each Data Point must have (a fairly long) part of any formula that returns them. Formulas get exponentially harder to understand and maintain, the longer they are.

An Extremely simplified formula example:
If(And(1, <>~,<>^),1,IF(And(1,~,<>^),1,IF(and(1,~,^),1~^,IF(And(<>1,~,<>^,~,IF(And(<>1,~,^),~^,IF(AND(1,<.~,^,^,IF(AND(1<.~,^),1^,IF(Etc))))))))))))))))))))
A slightly simplified VBA Example:

Dim Result
Dim Cell1, Cell2, Cell3
IF Cell1 = "1" then Result = "1"
If Cell2 ="~" Then Result = Result & "~"
If Cell3 ="^" Then Result = Result & "^"
Cell.Value = Result

[jsb13]

Thanks for the reply. I've cut and pasted your questions below to make sure I've answered them all. Please let me know if any of this is unclear.


Your timeline sheet will grow vertically for every truck that enters a yard and horizontally every day that passes. What limits on this do you envision?

** Ultimately I think it will settle in at approximately 100 rows, though it's possible it could go higher.



You are wanting to display many different Data Points in a single cell. Possible, but hard to read and each Data Point must have (a fairly long) part of any formula that returns them. Formulas get exponentially harder to understand and maintain, the longer they are.

** Might make more sense in the "what" section below


Virginia trucks get special treatment on the Timeline ???

** Could you clarify? I'm not sure I know what you mean here.


You are currently only dealing with 4 states. Is it possible that that will change?

** Yes, it's possible but unlikely.



On the Timeline, MD and VA are one color and IL and WI are another. What is the significance?

** The colors are for visual separation and readability (see "what" below"). The color is implicit to the unit specifics, so IDing a unit as WM Freightlinger 590 automatically links it to MD, and "orange" as color. The two different colors correspond to regions (mid-west versus mid-atlantic).



Please read the quote in my signature. You have definately shown us how you are trying to accomplish what you want, but if we knew what you want, we might know a better way.

** The "daily log" place is our data input. As we collect data, we record it in this format, which allows us to capture a lot of specific information about each individual entry. People who work with the data every day can read this format and figure out what's going on, but people who are only tangentially familiar with operations look at it and are confused. Thus, the "timeline" is data output. It serves several functions:

1) it visualizes data in a linear way, stripping out some of the detail (which isn't included in the example since it's not relevant to the timeline display) and ultimately making it easier to follow
2) it allows people to see patterns that aren't readily apparent in the daily log, especially when activities are tied to units and specific days (i.e. 1^~, etc).
3) it allows us to more easily see the entire history of a particular unit. How often does it park at yards x, y, or z? How often is it involved in operation 1? How many days is it parked vice not parked?


So, I guess "what" I'm trying to do is come up with a way to display data over time more effectively, and for a way to do it without losing the benefits of #2 and #3 above. I'm certainly open to other ideas, this is just what we've come up with so far.

Thanks!

[SamT]

Virginia trucks get special treatment on the Timeline ???

** Could you clarify? I'm not sure I know what you mean here.See comments in Cells F12 and G13 on the Timeline sheet.

WHAT:
3) it allows us to more easily see the entire history of a particular unit. How often does it park at yards x, y, or z? How often is it involved in operation 1? How many days is it parked vice not parked?

How about something like this attachment?

[Bob Phillips]

Just formulae