I need some INDIRECT - ADDRESS help

[low98]

OK, taking the following code;

=IF(ROW($A7)-ROW($A$6)>COUNTIF(Sheet1!$E$1:$E$5000,$N$7),"",INDEX(Sheet1!$A$1:$A$5000,SMALL(IF(Sheet1!$E$1:$E$5000=$N$7,ROW(Sheet1!$E$1:$E$5000)),ROW($A7)-ROW($A$6))))

And making the range variable based on a cell (O7), this cell built off of variables and string text. It looks something like this;

Sheet1!$D$1:$D$5000

I need this to replace the Sheet1!$E$1:$E$5000 in the above range. I've tried using =INDIRECT(ADDRESS(7,15)), Cell O7, with no luck. Is this the best way to go about this? What am I doing wrong??

Thanks in advance!
Ryan

 

[Greg Truby]

book2
	A	B	C	D
1	a	1	A1:A7
2	b	5	B1:B7
3	a	1	a
4	b	5
5	a	1
6	b	5		4
7	a	1
Sheet1

<ul>[*]D7 =SUMIF(INDIRECT(C1),C3,INDIRECT(C2))[/list]