Is there an easier way

viper

Active Member
Joined
Feb 15, 2002
Messages
382
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:

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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
You could use a concatenated VLOOKUP to reference your course info:
Book1
ABCDEF
1CourseHoleConcatParYardageHandicap
2GreenRiver18GreenRiver1831252
3TustinRanch14TustinRanch1454753
4
5CourseHoleParYardageHandicap
6GreenRiver1831252
Sheet1


HTH,

Smitty
 

viper

Active Member
Joined
Feb 15, 2002
Messages
382
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
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
ABCDE
1CourseHoleParYardageHandicap
2GreenRiver1831252
3TustinRanch1454753
4
5CourseHoleParYardageHandicap
6GreenRiver1831252
Sheet1
 

Forum statistics

Threads
1,148,530
Messages
5,747,234
Members
424,070
Latest member
smanni3

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top