Is it possible to create a summary report?

**Huddle** · 08-20-2010, 01:45 PM

I have never used excel as a base for a report. Normally I use Access. Is it possible to create a summary report in excel?

(See the attached excel sheet.)

If it is what I am trying to do is make a weekly report of inspections that are outstanding. I know this will not be a simple task but I need direction on where to start.

I'd like to have something like this:

Week ending 11/14
Bldg CO0533 - 2 incomplete
Bldg CO0627 - 1 incomplete
Bldg CO1572 - all completed
Bldg CO1917 - 2 incomplete

**protonLeah** · 08-20-2010, 02:10 PM

Explain which cells you looked at to get your counts, and why count them.

**Richard Buttrey** · 08-20-2010, 02:21 PM

Hi,

Your data is not laid out in the most efficient way for analysing with Excel, but we first need to understand how you arrive at your answers above. Why for instance is Bldg CO1917 2 incomplete when column P for 14 November suggests all 4 are incomplete, and how can building CO0533 be 2 incomplete when there's apparently only one building in rows 79:82?

It would also be simplified if you had Excel 2007 rather than 2003. Are you planning to upgrade?

Rgds

**Huddle** · 08-20-2010, 02:41 PM

It's not be best set up I have to admit, but I had to tweak someone else's spreadsheet and this is what I was able to do.

The lines 78, 83, 92, and 101 are all count formulas.
78O counts the 4 fields below 79O:82O and divides it by 3 to get the number (the last field, 82O, will never have data just formatting)

The cells have conditioning formating depending on the number of inspections required.

HTML Code:

In this instance each building is required to have two inspections each week. If no inspections are done it's red, 1 inspection yellow, 2 or more is green.

I don't believe we will be updating anytime soon.

What I was going to do is just manually go through each building and write what is outstanding but since I have over 100 buildings I was looking for an easier way.

**Richard Buttrey** · 08-20-2010, 03:29 PM

Hi,

I'm attaching a suggestion.

See the Summary sheet. On the COFSL4 sheet I've added the Building number in the row immediately above the detail building rows, and given range names to column C 'Buildings' and row 2 ' dates.

Here's a tip. Try and avoid merging cells. They are a real pain and more trouble then they're worth. Havong mergec columns for dates will eventually catch you out. You can always use the centre across selection if you want to centre titles.

For other dates on the summary just copy D1:D8 across and change the dates

HTH