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

[tomeegee]

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 make modifications myself without running here with every little thing. But I've hit a wall with =@INDEX(INDIRECT, which is the core of my workbook. On the attached samples, the "Old Book" on worksheet ParHCPs shows the functioning formula used to populate the scorecard par and the hole handicap (this is used throughout the full workbook). On the "New Book" I updated the number of players, reduced the number (and name) of courses (Sheet S22) and on sheet ParHCPs I modified the Array in order to populate the handicaps with and without a woman player. 

When I do that, the =@INDEX(INDIRECT looses its Reference and I can't understand why. Because of course, I don't understand =@INDEX(INDIRECT and try as I have I can't find a clear video lesson on what's going on here. 

Any help, advice, pointing to right direction would be greatly appreciated. 

[#2]

@tomeegee 

Formula works if you name the range ParHCPs!$C$25:$U$27 as KingPrince. Same for other ranges. "@" is not required.

Please see attached.

[#3]

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.

[#4 · Accepted solution]

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

[#5]

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!

[#6]

@tomeegee , glad to help