=INDEX(INDIRECT is shifting one column over

Tomeegee

New Member
Joined
Mar 9, 2021
Messages
30
Office Version
  1. 365
Platform
  1. MacOS
I've been using this page in a larger scoring workbook for sometime. I changed the names of the courses and their values for a new tournament, but for some reason two out of the four course in the pull down list are populating Row 9 one column over. The Legacy and RioSecco are correctly pulling from C-K and M-U depending on FRONT or BACK. But RedRock and Paiute are pulling from D-L and N-V respectively, even though the same formula is in play. Would love to learn why and how to fix if I see this again.

handicap book.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXZAAABAC
1
2Player
3Tom GMGolf Course:RedRock
4
5FRONT
6
7HOLE123456789Total
8Score Card Par434543443667
9Hole Handicap3151115179130
10
11123456789101112131415161718
12
13Legacy434453445363444534543672
14Men's Handicap139171517531118128621641410
15Ladies Handicap371591713511118614101241682
16
17RioSecco443443455364434543543672
18Men's Handicap151913311717514216184812106
19Ladies Handicap135151371117910421612818146
20
21RedRock543454344364534345443672
22Men's Handicap731511151791381261018162144
23Ladies Handicap515113111739741418612101628
24
25Paiute445344534364544345343672
26Men's Handicap515711131917314842161210186
27Ladies Handicap171117591315314612188101624
28
29
30If with Player7315111517913
31if With Gina M7315111517913
32
33Comparison3151115179130=@INDEX(INDIRECT($I$3),IF($C$3="M",2,3),IF($B$5="FRONT",COLUMN(B1),COLUMN(L1)))
34
ParHCPs
Cell Formulas
RangeFormula
C3C3=IF(B3="Gina M","F","M")
C7:K7C7=IF(B5="FRONT",C11:K11,IF(B5="BACK",M11:U11))
C8:K8C8=INDEX(INDIRECT($I$3),1,IF($B$5="FRONT",COLUMN(B1),COLUMN(L1)))
L8,V25,L25,V21,L21,V17,L17,V13,L13L8=SUM(C8:K8)
C9:K9C9=INDEX(INDIRECT($I$3),IF($C$3="M",2,3),IF($B$5="FRONT",COLUMN(B1),COLUMN(L1)))
W13,W25,W21,W17W13=SUM(L13+V13)
C30:K30C30=IF(AND(B3<>"Gina M",$B5="FRONT",$I3="Legacy"),$C$14:$K$14,IF(AND($B5="BACK",$I3="Legacy"),$M$14:$U$14,IF(AND($B5="FRONT",$I3="RioSecco"),$C$18:$K$18,IF(AND($B5="BACK",$I3="RioSecco"),$M$18:$U$18,IF(AND($B5="FRONT",$I3="RedRock"),$C$22:$K$22,IF(AND($B5="BACK",$I3="RedRock"),$M$22:$U$22,IF(AND($B5="FRONT",$I3="Paiute"),$C$26:$K$26,IF(AND($B5="BACK",$I3="Paiute"),$M$26:$U$26))))))))
C31:K31C31=INDEX(C14:U27,MATCH(I3,B13:B26,0)+(C3="F"),SEQUENCE(,9,IF(B5="FRONT",1,11)))
C33:K33C33=INDEX(INDIRECT($I$3),IF($C$3="M",2,3),IF($B$5="FRONT",COLUMN(B1),COLUMN(L1)))
X33X33=FORMULATEXT(C33)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Legacy=ParHCPs!$B$13:$U$15C31
Paiute=ParHCPs!$C$25:$U$27C31, L25
RedRock=ParHCPs!$C$21:$U$23C31, L21
RioSecco=ParHCPs!$B$17:$U$19C31
Cells with Data Validation
CellAllowCriteria
B5List=Side
I3List=$Y$3:$Y$6
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Can you explain your named Ranges?

When I paste into a workbook, everything errors out
 
Upvote 0
I think I ended up solving your problem with my Question. If you reset your named ranges to what they should be, you will fix your problem. It looks like somehow Paiute and RedRock's Named Ranges got shifted over to C:U. Fix that, and everything starts to pull correctly
 
Upvote 1
Solution
I think I ended up solving your problem with my Question. If you reset your named ranges to what they should be, you will fix your problem. It looks like somehow Paiute and RedRock's Named Ranges got shifted over to C:U. Fix that, and everything starts to pull correctly
It had to be something simple! Thanks a ton. Fixed in a jiff.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top