Is there an easier way

[viper]

Hello all,

I can do this but my way will take me forever to code. I'm just seeing if there is an easier way to pull this off.

I'm making a golf scorecard. On one sheet I have the scorecard, on a second one I have all the course info: par, yardage, handicap, etc.

When I select a course from my list, the scorecard will populate with the course info. I'm using Select Case right now.

Here is the beginning code:

Dim CourseChoice As String
    CourseChoice = Sheets("Score Sheet").Cells(7, 1)
    
    Select Case CourseChoice
        Case Is = "Eagle Knoll"
            Sheets("Score Sheet").Cells(3, 3) = Sheets("Golf Course Info").Cells(5, 2)
            Sheets("Score Sheet").Cells(4, 3) = Sheets("Golf Course Info").Cells(6, 2)
    End Select

I can do the individual cells like above, but when it comes to putting in the yardage, par, and handicap for each hole, I'd prefer not to have to put 18*3 statements for each cell. Like the range on Sheet1 for each hole is C15:K15 and M15:U15 for par, C14:K14, M14:U14 for handicap, etc.

I have 6 courses to choose from. So doing it my way will be one lengthly Select case.

Thanks for any ideas and help.

 

[Oaktree]

Can you post a sample of your data?

 

[Smitty]

You could use a concatenated VLOOKUP to reference your course info:

Book1
	A	B	C	D	E	F
1	Course	Hole	Concat	Par	Yardage	Handicap
2	GreenRiver	18	GreenRiver18	3	125	2
3	TustinRanch	14	TustinRanch14	5	475	3
4
5	Course	Hole		Par	Yardage	Handicap
6	GreenRiver	18		3	125	2
Sheet1

HTH,

Smitty

 

[viper]

So PennySaver, can I use 3 in the Vlookup? Like this

=VLOOKUP($A6&$B6&$C6,$C$2:$F$3,COLUMN()-2,FALSE)

If I can I don't want to limit myself to one set of tees, So if I do it using Vlookup, I'd need to choose course, hole, and tees.


Just so, I've never been able to or wanted to use VLookup. Just don't understand it.

 

[Oaktree]

That is correct, as long as your concatenation has tees in it as well.

Alternatively, you could use array formulas if you don't want to use concatenated match keys.

Book3
	A	B	C	D	E
1	Course	Hole	Par	Yardage	Handicap
2	GreenRiver	18	3	125	2
3	TustinRanch	14	5	475	3
4
5	Course	Hole	Par	Yardage	Handicap
6	GreenRiver	18	3	125	2
Sheet1

 

[Smitty]

Sure you can use 3:

Book3
	A	B	C	D	E	F	G
1	Course	Hole	Concat	Tee	Par	Yardage	Handicap
2	GreenRiver	18	GreenRiver18Men's	Men's	3	125	2
3	TustinRanch	14	TustinRanch14Women's	Women's	5	475	3
4
5	Course	Hole		Tee	Par	Yardage	Handicap
6	GreenRiver	18		Men's	3	125	2
Sheet1

Smitty