# I need some INDIRECT - ADDRESS help

#### low98

##### Board Regular
OK, taking the following code;

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;

Code:
``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??

Ryan

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### Greg Truby

##### MrExcel MVP
book2
ABCD
1a1A1:A7
2b5B1:B7
3a1a
4b5
5a1
6b54
7a1
Sheet1

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

Replies
2
Views
75
Replies
7
Views
212
Replies
5
Views
159
Replies
13
Views
181
Replies
8
Views
151