Multiple Indirect functions in same equation

[ben_sorensen]

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]

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

 

[ben_sorensen]

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]

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]

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]

Awesome!! Im happy I was able to help