I need some INDIRECT - ADDRESS help

low98

Board Regular
Joined
Dec 21, 2004
Messages
116
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??

Thanks in advance!
Ryan
 

Some videos you may like

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
Joined
Jun 19, 2002
Messages
10,014
book2
ABCD
1a1A1:A7
2b5B1:B7
3a1a
4b5
5a1
6b54
7a1
Sheet1


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

Watch MrExcel Video

Forum statistics

Threads
1,118,760
Messages
5,574,079
Members
412,566
Latest member
TexasTony
Top