using a formula to name a range

New Contributor
I would like to base the name of a range on the contents of another cell. The cell it will be named after just has text in it. Essentially I want the name of the group of cells to be "=A2" where A2 would contain the name of a student - is this possible?
3 Replies

@joshman81 Not with a formula, but perhaps the "Create from selection", as described in the article link below is what you need.

https://support.microsoft.com/en-us/office/create-a-named-range-from-selected-cells-in-a-worksheet-f... 

Beware that Named ranges are bound to certain rules. The may not start with a number, contain spaces or other special characters. Such are preceded or replaced by underscores. So, if the cell with the name to be used contains John Doe, the named range will be called John_Doe . Similarly, a name like Doe, John will become Doe__Joe

@joshman81 

As @Riny_van_Eekelen says, not using a formula.  It is possible to use VBA; I used it to work round the inadequacies of Name Manager when uploading Lambda functions.

ActiveWorkbook.Names.Add LambdaName, RefersTo

Otherwise, I would recommend applying Names at the level of 'StudentNames' and then using XLOOKUP to return the specific data range.

= XLOOKUP(Name,studentName, Grades)

These days one could even use a Lambda function

= Gradesλ(Name)

"Gradesλ"
= LAMBDA(sn, XLOOKUP(sn,studentName, Grades))

 

 

@Peter Bartholomew Thanks for the help. Based on the feedback I ended up solving my problem using something other than the Names feature.