Summary Table's in Reports

[Knape97]

Hi Guys,

Fingers crossed you can help me out with this...

I have a table in a database (tbl_events), which includes thre
important fields;

EventType (Text - Lookup)
EventDate (ShortDate)
EcentLocation (Text - Lookup)

Now, i need the contents of this table summarised, so, for example..
lets say the table contains the following data..


Code
-------------------

Type Date Location
A 01/01/06 Office1
B 01/02/06 Office1
A 01/02/06 Office1
A 01/02/06 Office1
B 01/01/06 Office2
B 01/02/06 Office2
A 01/02/06 Office2
A 01/02/06 Office2
A 01/02/06 Office
-------------------



Now, what I need is a report that shows a result similar to th
following...


Code
-------------------

Location = Office 1

January February
A 1 2
B 0 1

Location = Office 2

January February
A 0 3
B 1 1

-------------------


Is this at all possable?

Any help appreciated

 

[Pete_UK]

Assume your table is on Sheet1 occupying cells A1 to C10, i.e. you have
headers in row 1 - highlight A1 to C10 then Insert | Name | Create and
untick the "Left Column" box and OK. This creates the named ranges
Type, Date and Location.

Insert a new sheet and enter the following:

A2: Location:
B2: Office1
A3: Month:
B3: January
C3: February
D3: March and so on up to
M3: December
A4: A
A5: B
Highlight A2:M5 and copy to A7. Enter:
B7: Office2

In B4 enter this formula:

=SUM(IF((Type=$A4)*(Location=$B$2)*(MONTH(Date)=COLUMN()-1),1,0))

Do not press <enter> when you have typed it in, do <CTRL><SHIFT><enter>
at the same time, as this is an array formula. If you do it correctly
then Excel will wrap curly braces { } around the formula - do not type
these yourself.

Select B4 and <copy> then highlight C4 to M4 and press <enter>. Then
highlight B4:M4 and copy these cells to B5, B9 and B10. This should
give you what you want.

Of course, there are only 9 lines of data in your table, and in reality
there will be far more - ensure that the named ranges are adjusted to
cover the whole of your data.

Hope this helps.

Pete

 

[Pete_UK]

Slight amendment.

When you copy the formula to B9 you will have to edit it to point to
B7, i.e. change the $B$2 in the middle to $B$7 and do
<CTRL><SHIFT><enter> again. Then copy across and down as before.

Pete