# Multiple Indirect functions in same equation

#### ben_sorensen

##### New Member
So here is what I am trying to do:

=IFNA(IF(ISNONTEXT(U7),"",RANK.EQ(W7,INDIRECT(M7&N7):INDIRECT(O7&P7)+COUNTIF(\$W\$7:W7,W7)-1),"")

I am trying to establish a ranking on different items down a list so there are as many as 50 items and 20 subsets of each item, I put them all in the same list for the sake of simplicity of viewing, the issue that I had was that I need to rank them according to each items subsets, so item 1 would have 20 subset items that need to be ranked and so on down the line.

I can use one indirect in my equation no problem but it won't allow me to use two indirect functions like that, does anybody have any solutions for this. I AM DESPERATE!

#### FDibbins

##### Well-known Member
Give this a shot...
=iferror(IF(ISTEXT(U7),RANK.EQ(W7,INDIRECT(M7&N7"":"&O7&P7)+COUNTIF(\$W\$7:W7,W7)-1,""),"")

#### ben_sorensen

##### New Member
I still get an error when I do that equation, the error still appears to pop up from the indirect and it doesn't even color the O7 or P7 cells

#### FDibbins

##### Well-known Member
When I need to do an involved/complex INDIRECT, I do it in small parts.
Start off wit a fixed version of your formula, that you know that works (no indirect, just actual references)
edit the formula and remove the = so the cell just shows the actual formula
start putting the INDIRECT together, and use the Fx key (to the left of the formula bar) to make your INDIRECT product exactly what your "real" formula looks like

Once you have that part worked out, you can then put in the fiddly/fancy bits like error checking, IF etc

#### ben_sorensen

##### New Member
Wow, that got it to work, that is weird that just pulling up the old Fx box and typing it in there made it work. I knew that all of the equation worked just fine, because I normally have the same policy as you, I start out really simple and then build on from there, I guess going more "old school" helped me solve the problem.

THANKS A TON!

#### FDibbins

##### Well-known Member
Awesome!! Im happy I was able to help

1,081,727
Messages
5,360,913
Members
400,602
Latest member
newaqua

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...