MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Messy report to compile

Posted by Laura L on September 06, 2000 12:02 PM

I am trying to wrap my brain on the best way to approach this problem:

I have 40 Dealerships sending me their numbers for a Used Car Report. This includes the make , model name, year of vehicle, etc.. many columns in other words. I have created a template for them to fill out this information on. I need to find the easiest way to compile a list of all the cars sold from all the dealerships and have them sorted first by Make then by Year of the vehicle. With 40 templates of information coming in, no way of knowing how many rows of information I need to compile, etc... I need to make this as easy as possible since there will be others trying to compile this report through the sheets I create that know N-O-T-H-I-N-G about Excel. Anyone have any super great ideas on how I Should tackle this?

Posted by Doug on September 08, 0100 8:54 PM

The first thought that comes to mind is to place your templates in one file (maybe as tabs in a single workbook)and then on your summary / report sheet reserve an appropriate number of rows for the data from each dealership. Be sure to reserve more rows than you will ever need for each dealership. In these rows reference the cells in the template from each respective dealer (references to blanks will be included).
By doing this your data will always be "live" and all you need to do is to sort by the make and year rows for a current report.
If you want to avoid the need to sort you can have a further summary sheet that uses a vlookup and sum to list the # of 1999 Chevy's sold etc.
Hope this helps