200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hello, is there a way to use multiple ranges in function indirect?
How to give the ranges 1, 2, 3 and 4 to function indirect
In other words, I have these range
2403!m38:m88
2403!m94:m145
2403!m163:m214
2403!m221:m272
Each of which is located in a different cell
And if I give each of these cells to the function 3, all the numbers will return that range
But how can I return all the numbers in these 4 ranges
Hi Kar2rost and welcome to the Forum.
This answer ahs been revised now that you have attached a file (as an Answer- it should be part of your question really). Usrs should note that you use right to left columns.
Your question is actually about using a complex formula to return the highest ranked number (without duplicates) but for multiple ranges. In J33, your formula for a single range is:
=IFERROR(AGGREGATE(14,6,INDIRECT($J$29)/(MATCH(INDIRECT($J$29),INDIRECT($J$29),)=ROW($A$1:INDEX($A:$A,COUNT(INDIRECT($J$29))))),""&E33),"")I suggest you use LARGE anstead, combined with COUNTIF to eliminate duplicates.
In the attached revised file, cell N38 (green) has the range containing all values (and headings- which LARGE will ignore):
'2403!m38:m214In cell K3, we get the highest value (ranked 1st) from that range using:
=LARGE(INDIRECT($N$38),1)If we changed the second argument (k, the rank position) from 1 to a 2, we'd get the same again (since that value is duplicated). Instead, we count how many duplicates of K33 are in the range then add 1. The second argument becomes (in bold):
=LARGE(INDIRECT($N$38),COUNTIF(INDIRECT($N$38),">="&K33) + 1)then copy that down and all duplicates will be ignored.You can either remove them from your range (e.g put them in anopther column) or adjust the formula in K33 to ignore them i.e.
=LARGE(INDIRECT($N$38),7)Hope this works for you (please remember to mark this answer as Selected if so)