Array formula where the range varies in row numbers

[simmo86]

Hi all,

I have the following array formula to sort data (I cant use dynamic array because I need to run a formula off the results).

This formula works perfectly, but the range changes i.e. it isnt always down to row 66.

Please Login or Register  to view this content.

As soon as there are less than 66 rows, the formula returns an error.

Does anyone know how to make it work so it will always work no matter how many rows are in the lookup?

Many thanks in advance,

Alex

[MarvinP]

Hi simmo86,

You say you cant use dynamic arrays, but have have you considered Dynamic Named Ranges in your formula?
https://www.ozgrid.com/Excel/DynamicRanges.htm

[simmo86]

Unfortunately I cant use any other addins as I am building this for someone else to use on their machine.

Is there no way of adapting the existing formula so it will work with any number of rows?

[JeteMc]

Could you convert the source data into a table and then use structured references in the formula?
It may help to utilize the instructions in the banner at the top of the page to upload a sample of the source data. We would also need to know the version of Excel that is being used by the person for whom the formula is being written.
Let us know if you have any questions.