Blog Post

Excel Blog
3 MIN READ

Preview of Dynamic Arrays in Excel

JoeMcDaid's avatar
JoeMcDaid
Icon for Microsoft rankMicrosoft
Sep 25, 2018
July 1st 2020 Update Dynamic Arrays is now available to Office 365 users on all endpoints.    Until now, you wrote a formula for each value you wanted returned to the grid. One formula, one val...
Updated Oct 05, 2020
Version 11.0
excel
KanwalNo1's avatar
KanwalNo1
Iron Contributor
Apr 30, 2020

JoeMcDaid SergeiBaklan Wyn Hopkins Bill Jelen

 

Hi Everyone

 

My File shows "Calculate" mode even though the calculation is set to automatic and there is no such macro to stop the calculation.

 

Just found that I have used INDIRECT(CELL("address")) function in the following Dyanmic Array Functions, which is causing this behavior.

 

=SORT(UNIQUE(FILTER(TableA[PartyA],ISNUMBER(SEARCH(INDIRECT(CELL("address")),TableB[PartyB])),"not found")),,1,TRUE)

 

I deleted all such functions, but kept all the macros and named ranges which use Cell() functions and then "Calculate" disappeared.

Then entered =INDIRECT(CELL("address")) in one of the cells and entered some random values in some random cells. "Calculate" did not appear.

Which means that =INDIRECT(CELL("address")) itself is also not causing this behaviour.

 

Now I am confused why using the INDIRECT(CELL("address")) in Dynamic Array Functions is causing such an issue ?

 

Regards

Kanwaljit