Forum Discussion

tomeegee's avatar
tomeegee
Brass Contributor
Apr 22, 2022
Solved

Frustrated making changes to =@INDEX(INDIRECT (Mac 365)

I have a golf scoring worksheet that I received tremendous help on from this community last year. I've tried my best to learn what I was taught and understand what the formulas are doing so I can mak...
excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 22, 2022

    tomeegee 

    ParHCPs!$C$25:$U$27 means range $C$25:$U$27 in sheet ParHCPs. I'm not sure how Name Manager works on Mac, on Windows it looks like

    In New Book you have named ranges from Old Book, but you didn't define names for the ranges in New Book. For the KingPrince that is the range

    INDIRECT($I$3) takes cell from I3, which contains name of the range, and returns reference on the range $C25:$U$27. INDEX takes element from that range. No array is returned, just value. "@" is needed if you would like to return first element of the array, but that's not your case.

     

    Using of names is hard in maintenance if you change them from time to time. Alternatively you may use something like

    =INDEX( XLOOKUP($I$3, $B:$B, $C:$U), 1,IF($B$5="FRONT",COLUMN(B1),COLUMN(L1)))

     

tomeegee's avatar
tomeegee
Brass Contributor
Apr 22, 2022
Sergei! I remember you from last year. Thanks for replying. Maybe I'm missing something. I can't find where the "@" is I'm using other than the =@INDEX. I've pasted the array and the @Index from both our files and they appear identical. Yet yours does populate the Score Card Par and Hole Handicap from KingPrince (although it starts at D25/26 and not C25/26, so you can see hole 9 and the total are outta whack). I can't find ParHCPs!$C$25:$U$27 in my NewBook file.
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 22, 2022

tomeegee 

ParHCPs!$C$25:$U$27 means range $C$25:$U$27 in sheet ParHCPs. I'm not sure how Name Manager works on Mac, on Windows it looks like

In New Book you have named ranges from Old Book, but you didn't define names for the ranges in New Book. For the KingPrince that is the range

INDIRECT($I$3) takes cell from I3, which contains name of the range, and returns reference on the range $C25:$U$27. INDEX takes element from that range. No array is returned, just value. "@" is needed if you would like to return first element of the array, but that's not your case.

 

Using of names is hard in maintenance if you change them from time to time. Alternatively you may use something like

=INDEX( XLOOKUP($I$3, $B:$B, $C:$U), 1,IF($B$5="FRONT",COLUMN(B1),COLUMN(L1)))

 

  • tomeegee's avatar
    tomeegee
    Brass Contributor
    Apr 22, 2022

    I deleted my last question in case you already started reading...

     

    The NAME MANAGER was the ticket! I was able to go in and fix the original file with that. Great to learn how that works!  Thanks for sticking with me!

     

     

Resources