Generating Custom Timeline on Separate Tab

[jsboss]

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 (desired outcome)"). 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. 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!

Thanks,
Jeremy

[AZ-XL]

Hi
enter this formula to f2 cell and drag down and left
=COUNTIFS('Daily Log'!$E:$E,'Timeline (desired output)'!$E2,'Daily Log'!$D:$D,'Timeline (desired output)'!$C2,'Daily Log'!$C:$C,'Timeline (desired output)'!$B2,'Daily Log'!$B:$B,'Timeline (desired output)'!$A2,'Daily Log'!$A:$A,'Timeline (desired output)'!F$1)+
COUNTIFS('Daily Log'!$E:$E,'Timeline (desired output)'!$D2,'Daily Log'!$D:$D,'Timeline (desired output)'!$C2,'Daily Log'!$C:$C,'Timeline (desired output)'!$B2,'Daily Log'!$B:$B,'Timeline (desired output)'!$A2,'Daily Log'!$A:$A,'Timeline (desired output)'!F$1)

I hope this is what you want

[bsalv]

make a pivottabel with your data and you get it immediately without any formula.

[jsboss]

Thanks for the reply. That seems to be working, but I've added some complexity to capture more information in the timeline (see example). I've added an "activity" section, denoted in the daily log as a separate column with either an "x" if activity occurred or " " if activity did not occur. Is it possible to have a separate section in the timeline to denote when activity 1 or activity 2 happened? also, I've added a column for operation 1. I'd like the timeline to reflect operation 1 occurring tied to a specific truck: could the sheet return "1^" for a truck in the yard with operation 1 occurring? Finally, I've added multiple yards designated by a location, represented as a number. Could the timeline return "1~" if the location of the truck is greater than 2000, and just "1" if the location is less than 2000?

Please see the example for more details.

Thanks!
JeremyHelp_Example_2.xlsx

[jsboss]

I don't have a great deal of experience with pivot tables. Would that update automatically as new data was inputted everyday?