Determine if a file is open in Excel or Google Sheets
We currently use a mixture of Excel and Google Sheets. Not ideal, but it’s important that any output I create can behave with both platforms, there are inherent differences in the platforms.
Some of these differences simply can’t be accommodated for. Others, however, can be worked around
From time to time, I need my document to do something slightly different in Excel than for Google Sheets, so as to result in the same outcome.
For example, I sometimes use the Wingdings font to create custom character icons…
(The standard built-in icon sets for conditional formatting don’t allow for a negative value bring favourable)
However, this character set is not supported by gSheets, and therefore displays the underlying letters rather than the character I want to see…
And that doesn’t really help anyone
Therefore, when opened in gSheets, I want to display words rather than letters/icons.
But to do this, I need to know when the file has been opened in Excel, and when it’s been opened in gSheets.
Thankfully, this is pretty straightforward – all you need to do is find a simple formula which you know won’t work in gSheets, and then error trap it.
I chose to use the CELL() function.
This formula, in Excel, works correctly, and displays the work ‘Excel’
However, in gSheets, the formula fails, and returns ‘gSheets’.
I create a named range for this cell, so that I can then reference it later…
… or however you need to use it!
Now, when opened in gSheets, I get the word values