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!
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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
Joined
Jun 11, 2015
Messages
44
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
Joined
Feb 16, 2013
Messages
6,723
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
Joined
Jun 11, 2015
Messages
44
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!
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top