Multiple Indirect functions in same equation

ben_sorensen

New Member
Joined
Jun 11, 2015
Messages
44
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!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Give this a shot...
=iferror(IF(ISTEXT(U7),RANK.EQ(W7,INDIRECT(M7&N7"":"&O7&P7)+COUNTIF($W$7:W7,W7)-1,""),"")
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top