Lookup question/condition formatting, I think

[MistyLogic]

(I apologize in advance for the poor insertion of the excel-to-html sections. I can't seem to get the blank cells to show properly (" "=blank))(nevermind, they were only showing up in the preview)

I have a database of, sadly, virus alerts on our network that gets updated daily with new records. It basically looks like this:

Book2
	C	D	E	F	G	H
1	Date	Filename	VirusName	VirusType	ActionTaken	Computer
2	12/1/20039:46	play.exe	W32.Klez.H@mm	File	LeftaloneinQuarantine	PC-MARIA
3	12/1/20039:46	font.scr	W32.Klez.H@mm	File	LeftaloneinQuarantine	PC-MARIA
4	12/1/20039:46	hkcsrlh.exe	W32.Swen.A@mm	File	LeftaloneinQuarantine	PC-MARIA
5	12/1/20039:46	to	W32.Klez.H@mm	File	LeftaloneinQuarantine	PC-MARIA
6	12/1/20039:46	VOLUME.pif	W32.Klez.H@mm	File	LeftaloneinQuarantine	PC-MARIA
7	12/1/20039:46	application.pif	W32.Sobig.F@mm	File	LeftaloneinQuarantine	PC-CARL
8	12/1/20039:46	movie0045.pif	W32.Sobig.F@mm	File	LeftaloneinQuarantine	PC-CARL
Sheet1

On another sheet in the same workbook, I want to create a "matrix" that looks like this:

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		12/1/03	12/2/03	12/3/03	12/4/03	12/5/03	12/6/03	12/7/03	12/8/03	12/9/03	12/10/03	12/11/03	12/12/03	12/13/03	12/14/03
2	PC-KIP
3	PC-MARIA
4	PC-TEMP
5	PC-CARL
6	PC-ROD
7	PC-JANE
8	PC-ERIC
Sheet2

The red boxes above are manually shown right now. I want to create a formula for conditional formatting that will look up in the database above what days which machines got infected and make that box red in the the "matrix" below. I'm trying to see if certain PC's are getting infecting at the same time, etc. Any help would be most appreciated!

 

[GorD]

Hi, I think that a pivot table would be a good option. Are you familiar with them. Conditional formatting of the pivot table should give you exactly what you are looking for.

Book1
	A	B	C	D	E	F
1	Date	Filename	VirusName	VirusType	ActionTaken	Computer
2	12/1/20039:46	play.exe	W32.Klez.H@mm	File	LeftaloneinQuarantine	PC-MARIA
3	13/1/20039:46	font.scr	W32.Klez.H@mm	File	LeftaloneinQuarantine	PC-MARIA
4	13/1/20039:46	hkcsrlh.exe	W32.Swen.A@mm	File	LeftaloneinQuarantine	PC-MARIA
5	14/1/20039:46	to	W32.Klez.H@mm	File	LeftaloneinQuarantine	PC-MARIA
6	14/1/20039:46	VOLUME.pif	W32.Klez.H@mm	File	LeftaloneinQuarantine	PC-MARIA
7	12/1/20039:46	application.pif	W32.Sobig.F@mm	File	LeftaloneinQuarantine	PC-CARL
8	14/1/20039:46	movie0045.pif	W32.Sobig.F@mm	File	LeftaloneinQuarantine	PC-CARL
9
10	CountofVirusType	Date
11	Computer	12/1/20039:46	13/1/20039:46	14/1/20039:46	GrandTotal
12	PC-CARL	1		1	2
13	PC-MARIA	1	2	2	5
14	GrandTotal	2	2	3	7
15
16	Withconditionalformatting
17	CountofVirusType	Date
18	Computer	12/1/20039:46	13/1/20039:46	14/1/20039:46	GrandTotal
19	PC-CARL	1		1	2
20	PC-MARIA	1	2	2	5
21	GrandTotal	2	2	3	7
Sheet1

Does that do it

The conditional formats are simply cell value is >0, with both pattern and font set to red.

 

[MistyLogic]

Thanks, GorD, for your quick and insightful response! Yes, I am 'familiar 'with Pivot Tables and got it to work quickly with your solution. However, I have two follow up questions:

1. New records will be added to the database every day. Will the Pivot Table automatically incorporate these? They are added to the bottom of the list so I am assuming they will be.

2. The dates are listed with time-of-day, so I get a separate column for each separate time. I'd rather have it shown by whole day, like in my first example. As it stands now, the Pivot Table cannot show the whole timeline (it's too wide). I suppose I could flip the 'COMPUTER' list with the 'DATE' list so the table reads down instead of across.

Any suggestions?

 

[GorD]

With reagrd to point 1) as it stands you would need to reselect the data range and click refresh to add the new rows. You can get round this by using a dynamic named range. This is done using the insert name define menu, then in the refers to box type a formula using offset and counta. Syntax would be something like

=offset(1st cell,0,0,counta($a:a$),counta($1:$1)).

With regard to point 2), I'll need to think about that - would using another column and a = left(a1,9) be an option to extract only the date? Not sure?