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…
When you create a formula or a range that refers to cell A1 and you want it to ALWAYS refer to A1 no matter what – you have at least a couple of options.
(more…)
I have covered the new checkboxes in Excel in a previous post. Typically, they are used as a simple user interface to turn off or turn on an option. But I learned recently that you can also use them as a visualisation.
(more…)The scroll bar on the right of screen allows you to quickly scroll down to the bottom of your data. Sometimes the scroll bar thinks the data goes down to the maximum row number. It keeps track of what’s called the Used Range. The Used Range goes down to the intersection of the last used row and across to the last used column. To see where the Used Range ends press Ctrl + End. The cell selected may be empty.…