Getting information from multiple sheets

[quintin]

Good day,
Regular excel
I am needing assistance with the following issues. I have three sheets with data in them, the first one has a list of golf courses, the second has the courses (the name, par, slope, rating). On the third sheet is where I would like to enter the data as I played. Col A the date, col B a drop-down menu with a list of the courses, col C which tee, col D par of the course, col E the course rating, col F the slope. Thereafter are col’s that need to be filled in, in order to complete your round, and then at the end, you’ll be able to work out other information and handicap.

I am needing assistance with col’s c,d,e &f. so that once you select the course in the drop-down menu and selecting the color tee in col c, it should put the rest of the information from the other sheets into the blank spaces.

It goes down for 20 enters, from this I would then be able to work out other info.

I have tried the if function, index, match, and others, but I'm not sure.

I thank you for any assistance in advance.

Quintin

 

[Toadstool]

Hi Quintin,

I'm not sure if I followed you correctly so let me just give you my example sheets.

Names of Courses

Quintin.xlsx
	A
1	Course
2	Pebble Beach
3	PitchnPutt
4	St Andrews
5	Mike Pitch&Putt
6	Carl Crazy Golf
7
Names

Course details for each tee

Quintin.xlsx
	A	B	C	D	E
1	Name	Tee	Par	Rating	Slope
2	PitchnPutt	1	2	D	22
3	PitchnPutt	2	2	D	12
4	Pebble Beach	1	4	A	3
5	Pebble Beach	2	3	B	2
6	Pebble Beach	3	5	D	5
7	Pebble Beach	4	5	A	2
8	Pebble Beach	5	5	C	2
9	Pebble Beach	6	4	A	3
10	Pebble Beach	7	5	C	4
11	Pebble Beach	8	5	D	2
12	Carl Crazy Golf	1	5	A	2
13	Carl Crazy Golf	2	5	B	2
14	Carl Crazy Golf	3	4	C	3
15	Carl Crazy Golf	4	5	D	4
16	Mike Pitch&Putt	1	5	D	2
17
Courses

Games sheet where you enter the Date in A2, select Course Name from dropdown in B2 and the formulae fill in 20 rows of columns C,D,E and F.

Quintin.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Course	Tee	Par	Course Rating	Slope	Strokes Played	Strokes Recorded	Balls Lost	Clubs Broken	No. Clubs Thrown into Lake	Amount Owed to Swear Jar
2	05-Jan-21	Carl Crazy Golf	1	5	A	2
3			2	5	B	2
4			3	4	C	3
5			4	5	D	4
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Games

Cell Formulas
Range	Formula
C2:F21	C2	=IFERROR(INDEX(Courses!B$2:B$9999,AGGREGATE(15,6,ROW(Courses!$A$2:$A$9999)-ROW(Courses!$A$1)/(Courses!$A$2:$A$9999=$B$2),ROW()-ROW($C$1))),"")

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=OFFSET(Names!$A$2,,,COUNTA(Names!$A$2:$A$99))

 

[quintin]

Thank you for the formula's, I did everything as you have explained. However, I can only put info in the first cell B2, thereafter you are able to use the drop-down menu but, no information is placed in the other cells. Find attached screen shot.
Many thanks

 

[Toadstool]

I thought with maximum 20 rows you were going to just do one course at a time.
For the rows where there is no Course Name I need some reference after the first row. If there's always two tees for a course, or you only play one course a day and the date is repeated for every row where you expect data then I can figure something out.

Can you share the other sheets using XL2BB

 

[quintin]

Its works however, what I would like it to do is.

Col A is date

Col B is course (which is a drop-down menu)

Col C is the tee you would be teeing off from.

So once you have chosen your course, then in the next col C you would then have to choose (eg on which course colors may vary) See example

Course one Tee Red par of the course 68 course rating 62,4 slope 112

Course one Tee blue par of the course 68 course rating 61,4 slope 109

Other course may have up to four tee off areas.

Once you have selected the color tee then it should put the relevant information into col’s D (par of the course), E (course rating) and E (slope)


On a separate worksheet I have a list of the courses and etc

Col A course

Col B tees

Col C par of the course

Col D rating

Col E slope

Eg col A col B Col C Col D Col E

course one Tee red par of the course course rating slope

course one tee blue par of the course course rating slope

course two tee green par of the course course rating slope

course two tee blue par of the course course rating slope

and so it goes on with a total of 13 courses, however, the list is made up of 42, meaning that most of the courses have more than 2 tee off colors, the most been 4 tee off areas/colors.

I hope this makes more sense and must apologies for not been clear in the beginning.
Thank you
Quintin

 

[Toadstool]

OK, here's my amended Courses data sheet

Quintin.xlsx
	A	B	C	D	E
1	Name	Tee	Par	Rating	Slope
2	PitchnPutt	Red	29	D	111
3	PitchnPutt	Green	27	D	112
4	Pebble Beach	Red	72	78	145
5	Pebble Beach	Green	70	75.5	144
6	Pebble Beach	Blue	71	77	143
7	Pebble Beach	White	69	76	142
8	Pebble Beach	Yellow	71	75	141
9	Pebble Beach	Gold	72	74	140
10	Carl Crazy Golf	Red	32	66	122
11	Carl Crazy Golf	Green	29	55	123
12	Carl Crazy Golf	Blue	30	44	124
13	Carl Crazy Golf	White	31	33	125
14	Mike Pitch&Putt	Green	19	28	99
Courses

Here's the amended Games sheet. The formula in cell D2 can be copied right into E2 and F2 then D2:F2 can be copied down as many fields as you want.

Quintin.xlsx
	A	B	C	D	E	F	G	H	I
1	Date	Course	Tee	Par of the Course	CR	Slope	18 Hole Score	Adj Gross	Differential
2	05-Jan-21	Pebble Beach	Green	70	75.5	144
3			Yellow	71	75	141
4			Blue	71	77	143
5			White	69	76	142
6	06-Jan-21	PitchnPutt	Red	29	D	111
7	08-Jan-21	Mike Pitch&Putt	Green	19	28	99
8
9
Games

Cell Formulas
Range	Formula
D2:F9	D2	=IFERROR(INDEX(Courses!C$2:C$999, MATCH(1, INDEX(((INDEX($B$2:$B$99,AGGREGATE(14,6,ROW($B$2:$B$99)-ROW($B$1)/(($B$2:$B$99<>"")*(ROW($B$2:$B$99)<=ROW())),1)))=Courses!$A$2:$A$9997) * ($C2=Courses!$B$2:$B$999), 0, 1), 0)),"")
C8	C8	=IFERROR(INDEX(Courses!B$2:B$9997,AGGREGATE(15,6,ROW(Courses!$A$2:$A$9997)-ROW(Courses!$A$1)/(Courses!$A$2:$A$9997=$B$2),ROW()-ROW($C$1))),"")

Cells with Data Validation
Cell	Allow	Criteria
B2:B9	List	=OFFSET(Names!$A$2,,,COUNTA(Names!$A$2:$A$99))

NOTES:
1. You may want to lock the formulae cells as they're in the middle of your data entry area.
2. If a Tee is selected which is not valid for the course then no data is retrieved.

 

[quintin]

Good day,
I would just like to thank you for your patience, and your assistance.
I know a few things about excel, but there is always room for improvement.
Thank You
Quintin

 

[Toadstool]

You're welcome!
...and please ignore the formula in C8 as it was left over from a previous iteration.