Replace cell reference

[Rock5150]

Hello

I am using Mac Excel currently. I have a spreadsheet using golf scores. I would like my cell reference to point to a certain row only if a certain cell is listed as "F" for female as female (ladies) have a different handicap then the men. Since there are fewer ladies that play in our league, I figure a blank cell would note to use the common row and if the cell has an "F", then point to a different row

In the above, the person is a male, so the cell green is blank. In the sixth row with the A column named "Loveless, Albert Net", the formula, below, would point to row two (Handicap Men). The formula used in column B and out is as follows (the one below is Column B Hole 1). When the next column (C) is highlighted, the formula is (C$2), and so on ...(D$2, E$2, etc).

=B5-(B$2<=MOD($T6,18))-INT($T6/18)
=C5-(C$2<=MOD($T6,18))-INT($T6/18)

So if I put an "F" in the green cell above (T5), how can I change the B$2 to go to B$4, instead (row 4, Handicap (ladies).


I want to save time not putting "M" in the field for men as my formulas right now all point to row 2. If a female is playing in the tournament, I would manually change B$2 to B$4 cell by cell).

Thanks

 

[dreid1011]

Maybe this:

=B5-(IF(T5="M",B$2,B$4)<=MOD($T6,18))-INT($T6/18)