Forum Discussion
Shorter way or shortcut to use IF, AND, AND drawing from 3 variables
- I'm very impressed by this formula! I am tooling around with it to expand it to all courses and when the formula is on separate worksheets etc. But you've given me a great tool to work with! Thanks.
Hello again! I have been having a good time applying @Quadruple_Pawn's index formula. I also learned when not to, and just stick with nested IF. Though I did come across this challenge. In the attached spreadsheet, I have a nested IF (in C9) to populate the appropriate hole handicap based on course, and side. However, I have one woman player in my Players list, and the handicaps for her would all be different (except on Sand Hallow). So at the bottom I experimented with a modified IF to include =IF(AND(B3=Player (in C33) so that if any player from the list is in B3, the handicaps populate. And then I duplicated the formula and changed it to =IF(AND(B3="Gina M" (in C34) and have it populating the women's handicap. However, I'm not sure how to have both exist in the same formula without getting into another really, really long nested IF. They're pretty long already. Essentially I'm looking for an IF/BUT sort of thing, if there was one. Or am I getting to crazy for one golfer? I could eventually get more women, so I could have two lists, Mens, Womens.
Any help would be great. Is it possible to buy you guys a beer?
I suggest to add an IF statement in cell C3 that returns either F or M for female (in this example for "Gina M") or male based on the selected value in cell B3.
I adapted the named ranges e.g. "Sand Hollow" to "Sand_Hollow" according to the requirements of named ranges. Formulas in ranges C33:K33 and C34:K34 are updated accordingly and the suggested formulas are in C8 and C9 (copied across ranges C8:K8 and C9:K9) and it seems to work.
I am hitting wall on referencing =@INDEX(INDIRECT($I$3),IF($C$3="M",2,3),IF($B$5="FRONT",COLUMN(B1),COLUMN(L1))) on another worksheet. The name of this worksheet is ParHCPs. So I've tried referencing COLUMN(ParHCPs!B1) with broken results. I've been searching, but the other COLUMN formulas are treated differently, and I don't want to pollute what you've done.
The other issue is once I've attempted to use the INDEX(INDIRECT on another worksheet and trying to reference, my Lists have all gone wonky and look like this. My biggest fear is breaking everything!
- That's really incredible streamlined! I would imagine if I had more than one woman, I could create a W list and replace Gina M with that sublist? I look forward to applying this tonight to see it action. Thank you!
