Simple IF breaks after first match

[bbushue]

I'm attempting to execute a simple calculation when the value of a cell on my report sheet is found in a one-column range on a rawdata sheet. The other sheet includes many duplicates. The formula works on the first instance of a match, but subsequent matches fail.

With the help of CPearson (=IF(COUNTIF(Details!$A$1:A2,Details!A2)=1,Details!A2,""), i've tried building of list of unique values from which to execute my matching criteria...but, alas, no success.

Here's the detail.

=IF(Details!$A$2:$A$304=A:A,(SUM(MiniTables!$L$3:$L$7)/MiniTables!$M$3),"")

Details!$A$2:$A$304 is a range on another sheet with 250+ values, only 10 of which are unique. About 10 cells are blank.
A:A is a full list of real estate properties in a portfolio. I only want to report on them if there is activity as indicated by the "details!" sheet. If there is no match, i leave the cell blank. If there is a match, i do a simple calculation:
Sum a range of cells (L3:L7) and then devide it by a number (M3).

I have no hair left to lose on this one. A little help here?

bb
[/code]

 

[Aladin Akyurek]

Create a scaled down sample (20 rows at most) that reflects the structure of your data and post it here along with desired results (no formulas) by means of HtmlMaker.

 

[bbushue]

Below is the REPORT sheet. The content of this sheet is refreshed daily and will grow to 10,000 rows.

WeeklyDashboard01.xls
	A	B	C	D
1	RevenueYTD
2	Property	YTDOngoingCosts
3	ADT
4	ALX
5	ANN
6	AUH	$601
7	BAM
8	BCH	$601
9	BLA	$601
YTD

Below is the Raw Data Sheet (DETAILS)

WeeklyDashboard01.xls
	A	B	C	D	E
1	SiteCode	pYear	pMonth	pDay	DOW
2	AUH	2004	1	19
3	AUH	2004	1	20	2
4	AUH	2004	1	21	3
5	BCH	2004	1	27	2
6	BCH	2004	1	29	4
7	BCH	2004	1	30	NULL
8	BCH	2004	1	31	NULL
9	BCH	2004	2	8	NULL
10	BLA	2004	1	27	NULL
11	BLA	2004	1	29	NULL
12	BLA	2004	1	30	NULL
Details

Below is a slice of the MINITABLES Sheet from where the calculation is made. Ideally this is in a separate file, but i have cut and pasted into this workbook for the sake of trying to solve this problem...

WeeklyDashboard01.xls
	K	L	M	N
1	Actual			Forecasted2005
2	Capital	Ongoing toEOM	LiveProperties	Ongoing
3	$6,803	$5,814	18	$366,405
4	$123,083	$0		$50,000
5	$15,898
6	$7,249	$5,000		$150,000
7	TBD	TBD		$65,000
MiniTables

Hope this helps.