Originally Posted by
6StringJazzer
I did another review of your spreadsheet, understand a little more about what you're actually doing, and now wonder why the original formulas were so complicated. For example, the original formula in column AA is
=IF(Mon!$V6="50/50","50/50",IF(Mon!$V6="W","W",IF(Mon!$V6="D","D","")))
But according to header in Mon!V5, the only possible values are 50/50, W, D, or null. Why do you check each possible value here? Basically this says if the user enters an illegal value, show a null. If you want to restrict what the user can enter, then I would recommend Data Validation on Mon!V5. I have done that and this simplifies the other formulas quite a bit. This also solves the problem that you noted.
Thank you 6StringJazzer, it looks like my main problem was not knowing the INDEX function. I amended the formula proposed by you and it works like a charm.
martindwilson also gets the credit for help, he came up with INDEX function as well.
Problem solved.
Bookmarks