3

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.

  • Welcome. The question isn't completely clear regarding what kind of response are you looking for. – Rubén Apr 24 at 6:18
  • I tried to be clear in the opening statement: "Does GSheets allow this?" where this has been defined as "defined names which can be used to define constants/literals". The rest of the question is about giving a clear example for anyone to reproduce and make sure they understand what I'm trying to accomplish? All of the answers seem to be on point for what I was expecting but the accepted one was the most comprehensive in explaining why it isn't allowed while the others gave alternatives to get around the lack of support. How might I have been more clear in the ask? – cclark May 10 at 23:44
0

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

1

This could be also resolved by storing variables/constants into the script and then call for it like:

var variables = [2.54]
function cm_per_inch() {
  return variables[0];
}

0

=ARRAYFORMULA(A2:A5*cm_per_inch())

=A7*cm_per_inch()

0

cm_per_inch appears to be missing, presumably because GSheets does not support a defined constant.

You are right, Google Sheets doesn't support a defined constant as well it doesn't support other Excel features like Excel tables as many others.

Please send your feedback directly to Google by using Google Feedback.

It's worth to note that Google Sheets also have many features that are not supported on Excel like the built-in IMAGE(url) among others.

The alternatives are too many, from a) importing only the Excel values to b) create a new spreadsheet from scratch.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.