# Golf Scorecard Automation

#### wayne0881

##### Board Regular
Hi All,

I'm working on a little fun project - but have come to a sticking point.

i'll give an example with a small data set:

in B11-B15 I have the name of a golfer in C11-T15 I have where I want the automation to happen (scores over 18 holes)
in C2-H2 I have a table of data (C2 is the min score on a par 3 for example 1, D2 is the maximum score on a par 3 for example 4 (E2-F2) will be par 4, min 3 max 5 (G2-H2) will be scores for par 5's, min 3 max 6)
in C9-T9 I have the Par of each hole (3-5)

At the moment I just have a manually typed Randbetween function

Example:

If hole one was a par 5 i'd have in C11 (for golfer 1) =RANDBETWEEN(\$G\$2,\$H\$2) and just copy that down.

but the problem I have is when I change the pars for the holes in row C9-T9 i'll have to manually change the data in C11-T11 to match the par.

I'd ideally like the spreadsheet to look at the par of the hole C9 and then return the correct random number between the min and max values in C2-H2

So if hole 1 was a par 3 it would return a random number between C2-D2, for a par 4 E2-F2 etc...

Hope this makes sense - it's just a bit of fun and just can't put it down until I can work it out ))

I've looked at index,match,randbetween, vlookups all on Youtube and am close but just can't quite get it, so will make do with a manual function for now!

Thanks in advance for any input.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### Michael M

##### Well-known Member
It would be so much easier if you either used XL2BB (see my tag) to post a sample of your data
OR better still upload to a hosting site, DropBox for instance, and then post the link back here.

#### wayne0881

##### Board Regular
ABCDEFGHIJKLMNOPQRST
1Par 3Par3Par 4Par 4Par 5Par 5
21-352.521.4843536
336-753.521.48535.837
476-2004.521.4863638
5
6
7
8Hole123456789101112131415161718
9Par544345434445445345
10
111Dustin Johnson545356433356456443
122Jon Rahm453246443343354236
133Justin Thomas544346523544356455
144Collin Morikawa555436525345445334
155Xander Schauffele453354423555535336
166Bryson DeChambeau544334325444356336
Scores
Cell Formulas
RangeFormula
B2:B4B2=D2-C2
C11:C16,H11:H16,N11:N16,Q11:Q16,T11:T16C11=RANDBETWEEN(\$G\$2,\$H\$2)
D11:E16,G11:G16,I11:I16,K11:M16,O11:P16,S11:S16D11=RANDBETWEEN(\$E\$2,\$F\$2)
F11:F16,J11:J16,R11:R16F11=RAND()*\$B\$2+\$C\$2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R11:R45Cell Value=1textNO
J11:J45Cell Value=1textNO
F11:F210Cell Value=1textNO

#### Michael M

##### Well-known Member
Maybe this in C11
Excel Formula:
``=IF(C\$9=5,RANDBETWEEN(\$G\$2,\$H\$4),IF(C\$9=4,RANDBETWEEN(\$E\$2,\$F\$4),RANDBETWEEN(\$C\$2,\$D\$4)))``
Then drag down to C16 and across to T16

#### wayne0881

##### Board Regular

Maybe this in C11
Excel Formula:
``=IF(C\$9=5,RANDBETWEEN(\$G\$2,\$H\$4),IF(C\$9=4,RANDBETWEEN(\$E\$2,\$F\$4),RANDBETWEEN(\$C\$2,\$D\$4)))``
Then drag down to C16 and across to T16
Looking good - that seems to have done it

#### Michael M

##### Well-known Member
Ok...fingers crossed...

#### Peter_SSs

##### MrExcel MVP, Moderator
I think this does the same job.

Excel Formula:
``=RANDBETWEEN(INDEX(\$C\$2:\$H\$4,1,C\$9*2-5),INDEX(\$C\$2:\$H\$4,3,C\$9*2-4))``

Replies
8
Views
774
Replies
9
Views
216
Replies
6
Views
174
Replies
7
Views
197
Replies
4
Views
56

1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

### 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.

### Which adblocker are you using?

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

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