tracking reoccuring things

[maradykstra]

I currently track cars that illegally park in a parking lot. I get data everyday and I cross reference existing data to new data I get by using the vlookup to see if I get the same car that illegally parks. My data I get each day is stored in a tab in my worksheet but I'll be tracking this for a long time so each day I get new data I put that new data in a new tab and use the vlookup with my master tab to the new data/tab. So far it's fine, but I'm going to be doing this a for quite some time so I can't have a row of new tabs for each day.


Is there a better way I can do this without having to use my tabs for each day I get with new data? I can put the data each day in one sheet but can I still use my vlookup? I hope this makes sense.


Any suggestions and thoughts is appreciated!

 

[Hui]

Yung

My general approach to storing data is Excel is to put it in one large table with appropriate fields and not to segregate the data as you are doing.

You can then setup a few standard reports, like

1. Report all Breaches on a date, with Repeats shown next to name

2. Report Top 10 etc Breaches by name or Date

3. Report history for an individual


Excel Data Tables allow most of this natively


The data fields you will likely need will be something like

Date, Time, Vehicle Licence, Name, Car Type, Comments etc

 

[maradykstra]

Thank you Hui for your suggestions. If I do the one large table then can I still do a vlookup? I have to see if the licenses I collect will match the new set of licenses I get each day. That's how I know if the same car is still illegally parking.


Thanks again for your help.

 

[Hui]

If you have one large table set it up as a Table

Excel 2007 - Insert Tab, Table


The Table has a pick drop down for each field that allows you to sort and select via various fields


Try one and see what you think