Creating scoresheet to autofill selected areas

[rcsanders]

Hello all,

Assigned with a project of creating a score sheet for our local netball club, in my search i came across this thread which is something similar i was trying to achieve.

http://www.excelforum.com/excel-gene...coresheet.html

From the sheet attached, i was hoping to autofill names of the teams (Highlighted in Green) and to populate details (Highlighted in Red) and populate the corresponding players from those teams (Highlighted in Yellow) on the score sheets but not sure what will be the simplest way to do it.

As you can see i have the main sheet SCORE SHEET TEMPLATE which will be used to print once it is populated with the teams and their players (Under 11 Teams) for each round (Under 11 Fixture). I was thinking to use a simplified drop down menu on the SCORE SHEET TEMPLATE to automatically populate the names but not sure what the best way to go about it would be (or easiest).

I am happy to plough through it once i have a bit of guidance and look forward to compiling it exactly how i would like it. Ultimately there would be many grades (Under 11 - 17) and will save so much time from the current cut and paste regime they are now using.

All help and suggestions are most appreciated.

Regards
Rob

[rcsanders]

Hello all,

Scoresheets-sample #2.xls

Starting to enjoy the fundamentals of learning Excel.

So far i have got to the stage where i will assign a game an id and grab the data (vlookup) from the sheet. Is there a better way of doing this as we need to print these sheets out for each round?

Now i am stuck how to populate the players name towards the bottom of the sheet (highlighted in yellow) from teams reference page (Under 11 teams).

Can anyone shed some light here please?

Regards
Rob

[rcsanders]

Almost there, but stuck on the following formula @ C25 on front sheet to pull in team names:

=INDEX('Under 11 Teams'!$B$2:$B$34,SMALL(IF($A$2:$A$34='SCORE SHEET TEMPLATE '!$C$6,ROW($A$2:$A$34)-1),""))

Regards
Rob

[rcsanders]

double post

[rcsanders]

Any suggestions ??

[newdoverman]

This may give you a hand.

[rcsanders]

You sir, are now on my Xmas card list.

Thank you very much.

If you are able to, could you explain this ROWS($1:1) a little please?

This may give you a hand.

[newdoverman]

ROWS($1:1) is for a lack of better terminology a method of counting vertically on the worksheet. To test this, on any worksheet and any cell, enter =ROWS($1:1) when you hit enter you will get a 1. Now, if you copy that formula down the column it will increase 1,2,3,4,5 etc as far as you go. This is useful in with functions such as LARGE and SMALL where you have an array and K. K is a value that determines what value from the array that you want. If you are wanting several values from an array in order that is where the ROWS($1:1) can come into use.

[rcsanders]

Again, very much appreciated.