The INDEX function can return a reference to a cell. However, the new TRIMRANGE syntax won’t accept it a reference. You need to use the TRIMRANGE function to use INDEX or INDIRECT.
In my last post I showed how to always refer to cell A1 in a reference.
The techniques demonstrated in that post won’t work with the new TRIMRANGE syntax. – see this previous blog post about the new syntax.
If you try to use the new syntax, you will see the following error message.
You can use the TRIMRANGE function to apply this new functionality with INDEX – see image below.
The ,2 in the TRIMRANGE function means a flexible ending cell reference based on cell entries.
The TRIMRANGE function is also required for the INDIRECT function – image below.
