Office 365 SORTBY Function not working with INDIRECT and Office ProPlus

[ExcelWombat]

I have a spread sheet that uses the SORTBY function with a formula that looks like this =SORTBY('Data'!B4:D4445,VALUE('Data'!H4:H4445),-1) and it works on all versions of Office 365 as well as Office Pro Plus 2019.

However, when I use the formula in conjunction with the INDIRECT function like this =SORTBY(INDIRECT(AX30),INDIRECT(AY30),-1) it works in all versions of Office 365 but it will not work in Office Pro Plus 2019.
I have used INDIRECT because the size of the data varies and I use it in conjunction with MATCH.
However, it is not the formulae that is a concern, only that it does not work in one version of Office.

The formula is changed in the Pro Plus 2019 version to indicate the function is not available.

Can anyone explain why SORTBY used with the INDIRECT version will not work in Office Pro Plus even though SORTBY does work.

Any suggestion would be most welcome. I posted this question on the Microsoft Community website on the 7th of December but have had no responses.

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[ExcelWombat]

I have made some minor changes as requested. Thanks.

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

Please provide a link to your cross-post on the other forum (I need you to be in compliance with the forum rule on this before we proceed).

[ExcelWombat]

I have added an example of the spreadsheet using the functions SORTBY and INDIRECT.
The formulae in cells C9, I9,O9 and U9 all work perfectly in Office 365 including Office ProPlus.
The formulae in cells AL9 and AR9 which use the INDIRECT function works in all versions of Office 365 EXCEPT for Office ProPlus.
I would like to know why?

[hrlngrv]

Given my own antipathy towards unnecessary use of INDIRECT, just don't use it. First, summarizing your formulas.

Rankings!AR9:

Please Login or Register  to view this content.

Rankings!AX30:

Please Login or Register  to view this content.

For State = "NSW", this refers to AX32.

Rankings!AX32:

Please Login or Register  to view this content.

At this point, an alternative becomes clear.

INDIRECT(AX30) = INDIRECT("'Heat Map'!B7:D9") = 'Heat Map'!B7:D9 = INDEX('Heat Map'!B$1:B$27,AX54):INDEX('Heat Map'!D$1:D$27,AY54)

Which means the formula in Rankings!AR9 could be

Please Login or Register  to view this content.

I note that you have event handlers in your sample workbook to enable/disable calculation, which indicates to me that your full workbook has so many INDIRECT calls that normal recalc performance stinks. That's precisely what's wrong with INDIRECT: it's volatile, so ANYTHING which triggers recalc makes all formulas calling volatile functions like INDIRECT recalc. When those formulas are using INDIRECT as multiple cell range arguments to lookup or newer FILTER/SORT*/UNIQUE functions, that can be quite expensive in terms of processor usage.

INDEX(...):INDEX(...) isn't volatile, and if the 1st argument to each INDEX call refers to smallish ranges, they should be much more efficient.

Finally, I still have @#$%&*! Office 2013 at work (@#$%&*! SDI but none of the useful new functions, worst of all worlds), Office 365 Insider at home, but not Office 2019 Pro Plus, so I can't reproduce your actual error.

[ExcelWombat]

Thanks for your comments. I do understand your antipathy towards INDIRECT and it is used sparingly. The live spreadsheet has over 1 million cells with formula but it is not slow to recalculate. The fact that it all works with Office 365 is a clear indication that the use of SORTBY with INDIRECT is not a problem. It is only a problem for Pro Plus and i believe this to be a bug that Microsoft needs to fix.

[hrlngrv]

Even if INDIRECT isn't a performance drag, maybe INDEX(...):INDEX(...) works in ALL Excel versions. The fact that SORTBY(INDIRECT(...)...) is a problem in 2019 Pro Plus indicates it is a problem as long as anyone would need to use your workbook under that version.

Under Office 2019 Pro Plus, move to BA1 and enter =INDIRECT(AX30). What does that return? If that doesn't produce an error, what does =INDIRECT(AY30) return?

[ExcelWombat]

Unfortunately, I do not have 2019 ProPlus so I can't easily test variations in the formula. The spreadsheet is distributed to over 800 users and only one has this version of Office so it is not a big problem. It is just something that should work and doesn't and I don't know how to go about getting it fixed. Thanks for trying.

[hrlngrv]

. . . I don't know how to go about getting it fixed. . . .

You may need to ask the one user with that version to perform testing for you. Are they using the same language and regional settings as the rest of your users?

I'd ask them to send you a screenshot with AW30 the top-left cell in the worksheet. First thing to check is whether the calculated range references as text are calculating correctly.

[ExcelWombat]

I'll give it a try. Regards

[ExcelWombat]

I have not been able to contact the one user to get any testing done. Looking back on old emails between us the formulae in the cells at AW30:AY38 are all being resolved correctly. The formula at fault in AL9 and AR9 have changed and are prefixed with _xlfn which i believe indicates the formula is not supported. As both SORTBY and INDIRECT are used successfully but separately in the same spreadsheet this can't be true. It is only when they are used together and only in Pro Plus that it fails.

[hrlngrv]

If Excel is preceding SORTBY with _xlfn., then that version of Excel does not support SORTBY. Since INDIRECT has been in Excel at least since Excel 2 in the late 1980s, it simply isn't plausible it'd be so prefixed.

You may believe SORTBY works in other parts of the workbook for this one user reporting problems, but I'd be willing to bet into US$ 4 figures that isn't the case.

[ExcelWombat]

Unfortunately, I can't show definitive proof until I contact the user. However, I come close enough that you lose the bet. I have uploaded two images to https://spaces.hightail.com/receive/9m9KqT9AcE (sorry, I didn't know how to put them on this post). One image the user supplied with the very left hand edge of the image showing two columns that have worked correctly and the right hand columns that don't. The formula in the first two columns contains the SORTBY function which I have displayed in the second image. The last two groups of columns on the right contain the SORTBY and the INDIRECT functions. I have also uploaded an image from my PC that shows the same columns with them all working.

[hrlngrv]

I don't lose anything if you can't show an _xlfn.

The 1st screenshot is identified as Office 365, which seems reasonable given the @#$%&*! search box squeezing out the QAT. The section heading in T7:X7 in the 1st screenshot could be consistent with the 1st 3 columns shown in the 2nd screenshot, but the data is different as the top line of data in the 1st screenshot is NSW | 2042 | $6,530,000 while the corresponding topmost row in the 2nd screenshot is VIC | 3142 | $6,530,000. The two screenshots don't seem to show the same data. Also, the 2nd screenshot doesn't show the formula bar, so it's not clear what would have been calculated or even whether anything was calculated. For instance, saving your sample workbook as .XLS and opening it in Excel 2000 (running under Linux), I see

deleteme-XLF.png

which makes it appear that U9:X32 has come through via SORTBY, but I can assure you there's no SORTBY in Excel 2000. Instead, Excel prompted me whether I wanted to leave the results from unrecognized functions as-is upon opening the workbook, and I replied OK. I figure that's exactly what your one user with Pro Plus did.

Note also the absence of a CALC indicator in my screenshot. Excel treats all cells which had been populated by SORTBY in Office 365 as if they were constants in Excel 2000.

[ExcelWombat]

The reason why the values in the first few columns is different is because the data changes over time. This problem first occurred back in May 2020 and that is when the first screen shot was taken. At the moment it is all I have. I cannot reproduce the error as it existed in May. The formulae are unchanged. What is need here is someone with Office 2019 Pro Plus installed to test the sample spread sheet I attached to prove that it doesn't work. A lot of speculation about what is and what was is not moving a solution any closer. Nor is it beneficial to speculate about the outcome in Office 2000, it is irrelevant. I understand the reasons why you don't believe me but this is also unhelpful. Perhaps there is someone else on this forum that can help, if not I'll post elsewhere.

[hrlngrv]

If it's your job to make this work for ALL your users, even the ones using 2019 Pro Plus, shouldn't you be the one to buy an Office 2019 Pro Plus license, install it, and find a fix?

From a different perspective, if SORTBY doesn't exist in Office 2019 Pro Plus, and this indicates it doesn't, but your workbook needs to work under Office 2019 Pro Plus, you may not be able to rely on SORTBY. From a purely practical perspective, wouldn't it be expedient to find alternatives to SORTBY?

. . . I understand the reasons why you don't believe me but this is also unhelpful. . . .

From my perspective, the most help anyone can give you if your workbook must work under Office 2019 Pro Plus is to tell you as often as necessary until it takes that you need to look for alternatives to SORTBY. Alternatively, convince this one user to upgrade to Office 365. Those are likely to be your only alternatives.

[Fluff13]

What is need here is someone with Office 2019 Pro Plus installed to test the sample spread sheet I attached to prove that it doesn't work.

No need, SORTBY is one of the dynamic array functions & is only available in 365.

[ExcelWombat]

If it is only available in Office 365 why does it work in Office 2019 Pro Plus. Why does it work in one formula but not in another when used with INDIRECT.

[hrlngrv]

You don't actually have 1st hand personal experience that it works in Office 2019 Pro Plus. You're making assumptions that it worked for your one user using that version, but as I pointed out a few responses ago, it's POSSIBLE that one user just opened the workbook and told Excel not to update formulas calling unsupported functions. IOW, SORTBY may not be producing the results this user has provided you in screenshots. The cells which appear to have results produced by SORTBY may have been (very likely were) produced by SORTBY by someone else using Office 365, saved with those values, that version of the workbook sent to the user using 2019 Pro Plus, and opened without updating formulas calling SORTBY but not INDIRECT.

Basically, the rest of us with experience using many different Excel versions and reading MSFT documentation simply believe you're misinterpreting the problem. However, by all means go on believing you're correct and the rest of us are wrong. Any year now someone with Office 2019 Pro Plus may read this thread, perform the test, and report back who's in the right.