Google-sheets – Is it possible to have Named Constants in Google Sheets

google sheetsgoogle-apps-scriptgoogle-sheets-named-ranges

Excel has a notion of defined names which can be used to define constants/literals. Does GSheets allow this?

You can see this in action in the following Excel screenshot:

Excel defined name as constant example.

Cell B2 has the formula =A2*cm_per_inch and via the name manager window, you can see cm_per_inch is a constant value of 2.54 and not a reference to a cell address.

When I import this spreadsheet into GSheets I get the following with #NAME? errors. Also, note in the GSheets Named ranges list there are only two entries whereas in Excel there were three. cm_per_inch appears to be missing, presumably because GSheets does not support a defined constant.

GSheets #NAME? error when parsing Excel defined name constants

One workaround would be to do as this Stack Overflow Q&A proposes but that is more of a lookup table and not a constant as per Excel constructs. You can see this in action in a revision of the previous GSheet here:

GSheet with a defined name referencing a cell with a constant value

Of note, I wasn't able to use the cm_per_inch as the name of my range and had to use a new range name entirely in order for the formulae to resolve properly.

Best Answer

Currently, this is not possible in Google Sheets, because Select data range field can intake only cell/array references. Therefore, best you can have is to create the second sheet where you will store your variables/constants and name them with Named ranges - and then right-click the sheet and select Hide sheet

0

0

Related Topic