Help with design/brainst5orm

[lecet444]

I've been working on a project for the past few weeks, which brought me from a complete excel newbie to somewhat ok at vba
The problem was order tracking, inventory management, and monthly report.
the order tracking via PO numbers I set up a main sheet, that does the ordering

Excel Workbook
	AA	AB	AC	AD	AE	AF
12			P.O NUMBER		7403
13			DATE		14/06/2011
14			ABOVE ORDER NO. MUST APPEAR ON ALL PAPERS, PACKAGES, INVOICES AND CORRESPONDENCE RELATING TO THIS ORDER
15
16	item #	Qty ordered	part #	description	unit price
17
18
19
20
21
22
23
24
25
26
PO

Excel Workbook
	A	B	C	D	E	F	G
28	June 16, 2011	7426	toools		Mike		$ 507.15
29	June 16, 2011	7427	toools		MIke		$ 33.00
30	June 16, 2011	7428	toools		Mike		$ 235.00
31	June 16, 2011	7429	toools		Mike		$ 25.00
32	June 16, 2011	7430	toools		MIke		$ 25.00
33	June 16, 2011	7431	toools		Mike		$ 2.00
34	June 16, 2011	7432	toools		Mike		$ 310.50
35	June 16, 2011	7433	toools		MIke		$ 2.00
36	June 16, 2011	7434	toools		Mike		$ 5,649.95
37	June 16, 2011	7435	toools		Mike		$ 5,649.95
38	June 16, 2011	7436	toools		MIke		$ 5,649.95
39	June 16, 2011	7437	toools		Mike		$ 5,649.95
40	June 16, 2011	7438	toools		Mike		$ 5,649.95
41	June 16, 2011	7439	toools		MIke		$ 5,649.95
Excel 2010 I wrote the code to save it automatically with the PO number, and record the PO number as a hyperlink on a master sheet looks like this MASTER SHEET

Excel Workbook
	A	B	C	D	E	F	G
6	PART NUMBER	COST/PART	INVENTORY	AWAITING	RECEIVED	CONSUMED	NEW
7	abc 1	1	10	80
8	abc2	2	27	-50
9	abc3	3	5
10	abc4	4	21
11	abc5	1	161	-161
12	abc6	2	5
Excel 2010 then I made a current inventory tracking sheet Report

Excel 2010

I want to do a monthly report
I want an idea of how to algomate everything in one, where you would either choose dates or po numbers, and it would display, what was ordered for that period (based on po numbers in master sheet), and what has been recieved.
my problem is I would have to open all the hyperlinks in mastersheet to get information on parts ordered. is there an easier way. since for example a 3 months report could open up to 90 workbooks to extract information.