GUSS Scouting System

[theCoder]

I’m putting the finishing touches on this years scouting system and decided to make it available to anyone interested. The program is called “Grungy Universal Scouting System” (GUSS). GUSS is an Excel based scouting system written in Excel 2007. Use it as is, or hack it up as you see fit. System documentation is on the “Notes” tab. It comes pre-configured with the Oregon Regional teams and a few dummy matches pre-configured to let you play with it a bit to see what it does and if you want to use it.

We’ll be up in the stands at the Oregon Regional, watching team 956’s “Ice Cube” slide around the floor. Hopefully that is. I’d rather be watching the matches than helping our programmer blindly tweak autonomous code in the pits.

The system is available <HERE>

Any and all feedback before and after your regional would be greatly appreciated.

[theCoder]

Rev b improvements:
* Added match winner forecast
* Corrected mandate that one team win. Added ability to have a tie match.
* Added more data validation / error traps.

[wilsonmw04]

I'm looking over the software and here are my first thoughts:

1. I had to upgrade to 2007 to use it. Annoying? yes. Did I get a free 60 day trial? yes. Will I keep 2007? I have no clue.

2. not sure yet, I've only been playing with it for 10 minutes. I'll let you know.

What do I plan on doing with it? I'm going to try and put the teams for VCU in the system and run some imaginary matches. Or run the results from last year though it and see what It comes up with.

[JesseK]

It looks good so far -- one tip at the moment:

Since most people may want to customize the 'title' of each weighted factor for their own clarity, perhaps you could have all of the other areas that use the titles key off of that cell. That way, when the titles are changed in the Attributes and Weights tab, all other appropriate cells remain uniform so there's no confusion.

Example
"Match Input", cell A6 & "Paper Sheet", cell A7 both should contain the formula
='Attributes & Weights'!A$2

/edit
Also, is there a way to get all of the attributes to have the options of 0, 1, and 2?
I'd like 'healthy' to be "0 - hardware/software issues; 1 - autonomous issues; 2 - no issues(default) and
'Feed HP' to be "0 - no empty cells delivered (default); 1 - empty cell assist/delivery; 2 - delivered multiple empty cells"

[wilsonmw04]

ok, I've been playing with it for a bit and I've run into a few things:
1. When I populate my list of teams, they are not populated as a "hyperlink"
2. When I create new matches from my list and try to save them, I get an error: run-time error '1004' Unable to get the Vlookup property of the WorksheetFunction class.

I select "debug" and it points me to this line of code:
rownum = Application.WorksheetFunction.VLookup(WorksheetFun ction.Text(Str(teamnumber), "@"), Range("A2:Z500"), 19, False)

it seems to be in the part of the code that sets the new value in team page.

[kirtar]

Has anybody attempted to use this with Open Office?

[Pat Arnold]

I couldn't open the file since it's in Excel 2007... wish it was available in Excel 2003.

[ATannahill]

Originally Posted by Pat Arnold

I couldn't open the file since it's in Excel 2007... wish it was available in Excel 2003.

seconded

[JesseK]

Maybe you guys should "upgrade" rather than requesting that he downgrade...there's some really spiffy things going on in the background that 2007 does that can mostly be converted to 2003 formats.

Microsoft Office 2007 Converter

[theCoder]

Thanks for the input.

The most common problems I've seen so far have been associated with running directly from the download. Save the program when prompted, do not open it. A number of problems go away this way. If the buttons do not work, try to enable macros. This is done with the colorful button in the top left \ excel options \ trust center \ trust center settings \ macro settings \ "Enable" and check "trust access..."

I understand the desire for a 2003 version. I no longer have any computers with this version. I can do a save-as to the 2003 version, but I'm pretty sure there will be some problems with the macros. The differences may be minor and be a great learning experience for someone.

Someone asked about running this on Open Office (open source office for Linux). I'd be very surprised if it works. I use open office on my laptop and have noticed minor differences. There are a lot of semi-advanced macros under some of these sheets and I doubt the code has been implemented exactly.

Rev C
* Made observed attributes more configurable. Text on "Attributes" sheet, Column A will show up on other sheets and scouting paper sheet. Ranges are now all 0 (bad), 1 (medium/average), & 2 (good). Default values are also configurable.
* Added averages of all observed attributes on the team sheets. Now can see, for example, the % win of a team.

Worksheets in this system include:

Team List – List of teams, team names, current rank, average computed “score”, # matches played. On this screen is a button to automatically create one sheet per team. You may order the list by team # or by rank order. Double clicking on a team # will jump to the team sheet for that team.

Matches – The list of matches, each with the scheduled time, teams, and a forecasted winner based on previous team scores. Double clicking on a match id will automatically clear and populate the Match Input sheet.

Match Input –Match observations are entered in this sheet. The system automatically sets the default values for each observation/team to the configured default values. These values are set to a dark grey color initially. As values are entered, they turn black. This aids in data input so the scout can quickly double check that all entries have been made. If nothing extreme is observed, the default values will be used. This significantly reduces the amount of data collection required and entered. Space is provided for notes to be entered for each team. On this screen is a “Save Match” button. The save macro automatically populates information on each team sheet, copies team average values to the “Team List” screen, determines the rank order of all teams, then copies the rank values to the individual teams sheet.

Attributes & Weights – This sheet contains the list of 5 user configurable observations, their weight, % of total, default value, and description. The text value of the observation is automatically populated on the Match Input Screen, Individual Team sheets, and the Paper Sheet sheet. As delivered, the observations include: Robot Scoring Ability, Human Player Scoring Ability, Ability to feed human player / empty cells, Heavily Scored Upon, Healthy (died, had some problem).

Team Sheets – One sheet per team based on a configurable template. This sheet includes the team #, team name, # matches played, current rank, average values for all observations of the matches played, and all notes taken during the matches. Space is provided for a picture of the robot.

Notes – Contains revision log, and various notes about using the system.

[Daniel_LaFleur]

Originally Posted by theCoder

I understand the desire for a 2003 version. I no longer have any computers with this version. I can do a save-as to the 2003 version, but I'm pretty sure there will be some problems with the macros. The differences may be minor and be a great learning experience for someone.

Someone asked about running this on Open Office (open source office for Linux). I'd be very surprised if it works. I use open office on my laptop and have noticed minor differences. There are a lot of semi-advanced macros under some of these sheets and I doubt the code has been implemented exactly.
.

I've converted it to EXCEL 2003 and it seems to work just fine (granted I'm running it under EXCEL 2007 in compatability mode, so YMMV). I'll test it in open office later on today.

BTW, really nice setup

P.S. is there any way of adding additional weights easily and expanding the weights beyond 0-1-2?

[JesseK]

Originally Posted by wilsonmw04

ok, I've been playing with it for a bit and I've run into a few things:
1. When I populate my list of teams, they are not populated as a "hyperlink"
2. When I create new matches from my list and try to save them, I get an error: run-time error '1004' Unable to get the Vlookup property of the WorksheetFunction class.

I select "debug" and it points me to this line of code:
rownum = Application.WorksheetFunction.VLookup(WorksheetFun ction.Text(Str(teamnumber), "@"), Range("A2:Z500"), 19, False)

it seems to be in the part of the code that sets the new value in team page.

After playing with it a bit further, I too encountered this. After some investigation, it looks like it's a formatting error.

If you manually enter in each team number on the team list tab, the cells are formatted incorrectly and you will get this error. Hence, you need to copy/paste in a team list from somewhere else.

If you manually enter in the team #'s on the matches tab, then double click the match number, you see that in the 'notes' section you have your team #'s right aligned, whereas the original copy has them left-aligned. You also cannot double-click the team here or on the matches page to automatically go to that team's tab. The workaround for this one is to copy each team # cell from the team list tab over to the matches tab. Then it works perfectly.

Starting with the original, if you manually enter team numbers (e.g. 1130) for a new match, you will get an error that says the team is not found in the team list. This furthers my suspicions that all of the VLOOKUPS look at formatting.

Ok, so some bugs, and some workarounds, but I still like it

[wilsonmw04]

Originally Posted by JesseK

After playing with it a bit further, I too encountered this. After some investigation, it looks like it's a formatting error.

Ok, so some bugs, and some workarounds, but I still like it

I sort of ran into the workaround on accident when loading my old list into the new version. I did a cut and paste of my old list into the new sheet and problem solved. Things look really good so far. I look forward to playing with this a bit while watching the webcast of DC this weekend.

[theCoder]

Originally Posted by JesseK

After playing with it a bit further, I too encountered this. After some investigation, it looks like it's a formatting error.

If you manually enter in each team number on the team list tab, the cells are formatted incorrectly and you will get this error. Hence, you need to copy/paste in a team list from somewhere else.

If you manually enter in the team #'s on the matches tab, then double click the match number, you see that in the 'notes' section you have your team #'s right aligned, whereas the original copy has them left-aligned. You also cannot double-click the team here or on the matches page to automatically go to that team's tab. The workaround for this one is to copy each team # cell from the team list tab over to the matches tab. Then it works perfectly.

Starting with the original, if you manually enter team numbers (e.g. 1130) for a new match, you will get an error that says the team is not found in the team list. This furthers my suspicions that all of the VLOOKUPS look at formatting.

Ok, so some bugs, and some workarounds, but I still like it

I never saw the problem with the teams because I cut & pasted the list. I also ran into another vlookup issue with formatting. Today I was working on a "my team" page and I ran into problems where the team # was not being found in the team list.

Stay tuned....

[Zholl]

How exactly do you run this? I just downloaded it to check it out, and it downloaded as a zip file and I now have a folder with a ton of different files and folders, none of which are excel files.