Forum Discussion

joshman81's avatar
joshman81
Copper Contributor
Mar 22, 2022

using a formula to name a range

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?
excel
Formulas and Functions

3 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Mar 22, 2022

    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))

     

     

    • joshman81's avatar
      joshman81
      Copper Contributor
      Mar 25, 2022

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

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 22, 2022

    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-fd8905ed-1130-4cca-9bb0-ad02b7e594fd 

    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

Resources