logo
  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Determine if a file is open in Excel or Google Sheets

June 21, 2016 0 comments Article Easy, Excel

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.


=IF(ISERROR(CELL("filename")),FALSE,TRUE)

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…


=IF(ISEXCEL,"p","UP")

and


=IF(ISEXCEL,"q","DOWN")

… or however you need to use it!

Now, when opened in gSheets, I get the word values

Related

Tags: Conditional, Excel, Formatting, Functions, Google Sheets, IFERROR, Productivity, Rules

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Using the IFS() and SWITCH() Functions to test for more than one condition in Excel 2016
Creating Virtual Tables in Power BI Using DAX
Showing Data Bars as a 'Proportion to Total' in Excel
Populating one Drop Down based on the Selection of Another in Excel – Without VBA
Format or Remove Borders from a Slicer or Timeline in Excel

Meta

  • Register
  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Categories

Pages

  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Copyright System Secrets 2022 - Theme by ThemeinProgress