Excel VBA Course - From Beginner to Expert

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.)

problem with indirect for multi range

0

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

0

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:m214``

In 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)