Golf Scorecard Automation

[wayne0881]

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.

 

[Michael M]

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]

PGA Leaderboard.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1			Par 3	Par3	Par 4	Par 4	Par 5	Par 5
2	1-35	2.52	1.48	4	3	5	3	6
3	36-75	3.52	1.48	5	3	5.8	3	7
4	76-200	4.52	1.48	6	3	6	3	8
5
6
7
8		Hole	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
9		Par	5	4	4	3	4	5	4	3	4	4	4	5	4	4	5	3	4	5
10
11	1	Dustin Johnson	5	4	5	3	5	6	4	3	3	3	5	6	4	5	6	4	4	3
12	2	Jon Rahm	4	5	3	2	4	6	4	4	3	3	4	3	3	5	4	2	3	6
13	3	Justin Thomas	5	4	4	3	4	6	5	2	3	5	4	4	3	5	6	4	5	5
14	4	Collin Morikawa	5	5	5	4	3	6	5	2	5	3	4	5	4	4	5	3	3	4
15	5	Xander Schauffele	4	5	3	3	5	4	4	2	3	5	5	5	5	3	5	3	3	6
16	6	Bryson DeChambeau	5	4	4	3	3	4	3	2	5	4	4	4	3	5	6	3	3	6
Scores

Cell Formulas
Range	Formula
B2:B4	B2	=D2-C2
C11:C16,H11:H16,N11:N16,Q11:Q16,T11:T16	C11	=RANDBETWEEN($G$2,$H$2)
D11:E16,G11:G16,I11:I16,K11:M16,O11:P16,S11:S16	D11	=RANDBETWEEN($E$2,$F$2)
F11:F16,J11:J16,R11:R16	F11	=RAND()*$B$2+$C$2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R11:R45	Cell Value	=1	text	NO
J11:J45	Cell Value	=1	text	NO
F11:F210	Cell Value	=1	text	NO

 

[Michael M]

Maybe this in C11

=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]

Maybe this in C11

=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]

Ok...fingers crossed...

 

[Peter_SSs]

I think this does the same job.

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