Upvote Upvoted 35 Downvote Downvoted
ESEA S26 Team Manager
posted in Projects
1
#1
0 Frags +

Hi everyone, with season 26 upon us I wanted to find a way to give back to the community so I created this google docs spreadsheet that should be a really useful resource to both new and old teams alike. It includes tools to quickly find the ESEA page for a particular team using their team name or leader (and also supplies you with a link to their team leader's steam profile), a calendar-like roster manager so you can keep track of your team's availability, and a robust and powerful schedule page which allows you to choose date and time slots to keep track of your games week to week (and also includes a quick guide which you can copy and paste easily into any text chat showing an at-a-glance view).

Before I get into the particulars of some of the features I mentioned about, I want to give a really big shout out to the people I took inspiration from, and to those who assisted me in creating the final product:

Show Content
2w.Sovereign - I borrowed everything from the formatting to the formulas and picked his brain for 3 days straight about VLOOKUPs and complex logical operators

KevinIsPwn - Who created the original scrim scheduler spreadsheet and made me want to make something more elegant yet just as powerful

Newbie Mixes - Everyone who is apart of this project is really amazing and a large reason for making this was to help new players/team leaders get a grasp on what can sometimes feel like a really complicated task. I hope the new players that use this find it valuable

And a special thanks to Peppermint, Fygg, & Quest who all did some proofing for me over the span that I was working on this.

ESEA S26 Team Manager

To use this sheet for your own team, click the link above, click File -> Make a copy (Image)
This will save a copy of my spreadsheet to your google drive and you can then have full functionality of what it has to offer.

Features:

-Quick Team Lookup (Image)
This sheet allows you to search for a team using their ESEA team name (at time of creating this document), or their leader's ESEA alias and will give you a direct link to their team page, their team leader's steam profile, the contact's steam profile, and what division they are competing in.

-Roster (Image)
This sheet is used in a few ways, Column B (where you enter player aliases) is used to supply data to a drop down used on the schedule sheet, but the primary function is to track your team and subs availability as well as any regular ringers you use for scrims.

-Schedule (Image)
This sheet is the most complex of the three and is the main power house where everything comes together. Using the powers of data validation and vlookup, we can easily and quickly keep track of what teams I want to scrim against week by week and at what times, and also gives your group a chance to see what games have been scheduled/unscheduled or are tentative. There is really a lot going on here so if you aren't well attuned with excel based data manipulation I would advise against trying to hard edit anything on this form. I have set it up so Sunday starts the new week and there is enough scrim spots to extend down to Thursday with room for 2 scrim blocks per night.

Schedule Block: (Image)
-Classes: Dropdown that fills using roster sheet
--Will color appropriately based on which section selected player resides

Opponent: Dropdown
Contact: Auto-Fill Name + Link to steam profile
Away Connect: Auto-Fill (If available, you can add these to the teams sheet as you play throughout the season)
Map: Auto-Fill based on cell E2
Notes: Auto-Fill based on teams sheet
Div: Auto colors on team selection
Status: Manual confirmation if team has agreed to your proposed scrim

Quick Guide: (Image)
If you select cell J3:M11 (to select a range of cells, click J3, and hold down your shift key, then click cell M11), and press Ctrl+C on your keyboard, you can paste a conveniently formatted schedule to your teammates or other teams that are interested in scrimming you to get an ideal time slot that you have open.

Show Content
Sunday, 2017-9-24 10:30PM EST Scheduled vs. Faint Gaming Blue
Monday, 2017-9-25 9:30PM EST Scheduled vs. BLANC Esports
Monday, 2017-9-25 10:30PM EST Scheduled vs. Man Down
Tuesday, 2017-9-26 9:30PM EST Scheduled vs. IWHBYD
Tuesday, 2017-9-26 10:30PM EST Tentative
Wednesday, 2017-9-27 9:30PM EST Scheduled vs. mandela effect
Wednesday, 2017-9-27 10:30PM EST Scheduled vs. mandela effect
Thursday, 2017-9-28 9:30PM EST Scheduled vs. TwistedSinGG
Thursday, 2017-9-28 10:30PM EST Unscheduled
Hi everyone, with season 26 upon us I wanted to find a way to give back to the community so I created this google docs spreadsheet that should be a really useful resource to both new and old teams alike. It includes tools to quickly find the ESEA page for a particular team using their team name or leader (and also supplies you with a link to their team leader's steam profile), a calendar-like roster manager so you can keep track of your team's availability, and a robust and powerful schedule page which allows you to choose date and time slots to keep track of your games week to week (and also includes a quick guide which you can copy and paste easily into any text chat showing an at-a-glance view).

Before I get into the particulars of some of the features I mentioned about, I want to give a really big shout out to the people I took inspiration from, and to those who assisted me in creating the final product:
[spoiler]
[url=http://steamcommunity.com/id/GhosTagE/]2w.Sovereign[/url] - I borrowed everything from the formatting to the formulas and picked his brain for 3 days straight about VLOOKUPs and complex logical operators

[url=http://steamcommunity.com/id/KevinIsPwn/]KevinIsPwn[/url] - Who created the original scrim scheduler spreadsheet and made me want to make something more elegant yet just as powerful

[url=http://steamcommunity.com/groups/na6v6newbiemix]Newbie Mixes[/url] - Everyone who is apart of this project is really amazing and a large reason for making this was to help new players/team leaders get a grasp on what can sometimes feel like a really complicated task. I hope the new players that use this find it valuable

And a special thanks to Peppermint, Fygg, & Quest who all did some proofing for me over the span that I was working on this.
[/spoiler]

[url=https://docs.google.com/spreadsheets/d/18HK2H9sulpY3qxzUVKcEd46WWL1mrGR2PFggOJ1ZSdQ/edit?usp=sharing]ESEA S26 Team Manager[/url]

To use this sheet for your own team, click the link above, click File -> Make a copy ([url=https://puu.sh/xFxN5/ea496a0580.png]Image[/url])
This will save a copy of my spreadsheet to your google drive and you can then have full functionality of what it has to offer.



Features:

-Quick Team Lookup ([url=https://puu.sh/xFycO/3895d85235.png]Image[/url])
This sheet allows you to search for a team using their ESEA team name (at time of creating this document), or their leader's ESEA alias and will give you a direct link to their team page, their team leader's steam profile, the contact's steam profile, and what division they are competing in.

-Roster ([url=https://puu.sh/xFyph/2ca2e63655.png]Image[/url])
This sheet is used in a few ways, Column B (where you enter player aliases) is used to supply data to a drop down used on the schedule sheet, but the primary function is to track your team and subs availability as well as any regular ringers you use for scrims.

-Schedule ([url=https://puu.sh/xFyXc/10c2dfb09f.png]Image[/url])
This sheet is the most complex of the three and is the main power house where everything comes together. Using the powers of data validation and vlookup, we can easily and quickly keep track of what teams I want to scrim against week by week and at what times, and also gives your group a chance to see what games have been scheduled/unscheduled or are tentative. There is really a lot going on here so if you aren't well attuned with excel based data manipulation I would advise against trying to hard edit anything on this form. I have set it up so Sunday starts the new week and there is enough scrim spots to extend down to Thursday with room for 2 scrim blocks per night.

Schedule Block: ([url=https://puu.sh/xFzaa/8bd98a567a.png]Image[/url])
-Classes: Dropdown that fills using roster sheet
--Will color appropriately based on which section selected player resides

Opponent: Dropdown
Contact: Auto-Fill Name + Link to steam profile
Away Connect: Auto-Fill (If available, you can add these to the teams sheet as you play throughout the season)
Map: Auto-Fill based on cell E2
Notes: Auto-Fill based on teams sheet
Div: Auto colors on team selection
Status: Manual confirmation if team has agreed to your proposed scrim

Quick Guide: ([url=https://puu.sh/xFzbG/dd93288937.png]Image[/url])
If you select cell J3:M11 (to select a range of cells, click J3, and hold down your shift key, then click cell M11), and press Ctrl+C on your keyboard, you can paste a conveniently formatted schedule to your teammates or other teams that are interested in scrimming you to get an ideal time slot that you have open.
[spoiler]
Sunday, 2017-9-24 10:30PM EST Scheduled vs. Faint Gaming Blue
Monday, 2017-9-25 9:30PM EST Scheduled vs. BLANC Esports
Monday, 2017-9-25 10:30PM EST Scheduled vs. Man Down
Tuesday, 2017-9-26 9:30PM EST Scheduled vs. IWHBYD
Tuesday, 2017-9-26 10:30PM EST Tentative
Wednesday, 2017-9-27 9:30PM EST Scheduled vs. mandela effect
Wednesday, 2017-9-27 10:30PM EST Scheduled vs. mandela effect
Thursday, 2017-9-28 9:30PM EST Scheduled vs. TwistedSinGG
Thursday, 2017-9-28 10:30PM EST Unscheduled
[/spoiler]
2
#2
10 Frags +

this is good

this is good
3
#3
4 Frags +

i love wolsne

i love wolsne
4
#4
14 Frags +

Wolsne has been racking his brain and tearing his hair out for the past few days to make this work. I love Wolsne.

Wolsne has been racking his brain and tearing his hair out for the past few days to make this work. I love Wolsne.
5
#5
9 Frags +

As fucking sick as this is, (its really fucking sick) open teams have zero reason to schedule scrims and shit and should just be using discord or IRC or whatever. The only reason there should be schedules is for the very few teams that plan on also scrimming the invite teams.

Really nice job, but really should not be needed in the first place...

As fucking sick as this is, (its really fucking sick) open teams have zero reason to schedule scrims and shit and should just be using discord or IRC or whatever. The only reason there should be schedules is for the very few teams that plan on also scrimming the invite teams.


Really nice job, but really should not be needed in the first place...
6
#6
10 Frags +
Tino_As fucking sick as this is, (its really fucking sick) open teams have zero reason to schedule scrims and shit and should just be using discord or IRC or whatever. The only reason there should be schedules is for the very few teams that plan on also scrimming the invite teams.

Really nice job, but really should not be needed in the first place...

Thx, I really appreciate the feedback. I understand what you're getting at, but it has a lot more to offer than just the scheduling portion. Being able to quickly find out who you need to talk too to schedule your matches or scrims is really useful since ESEA doesn't link to players steam profiles by default, also the ability to keep track of your own teammates availability is nice too.

As far as scheduling, this is mostly used to maintain 'quality' scrims instead of getting match up last minute scrims versus teams are are drastically better or worse than the skill level of your own team

[quote=Tino_]As fucking sick as this is, (its really fucking sick) open teams have zero reason to schedule scrims and shit and should just be using discord or IRC or whatever. The only reason there should be schedules is for the very few teams that plan on also scrimming the invite teams.


Really nice job, but really should not be needed in the first place...[/quote]
Thx, I really appreciate the feedback. I understand what you're getting at, but it has a lot more to offer than just the scheduling portion. Being able to quickly find out who you need to talk too to schedule your matches or scrims is really useful since ESEA doesn't link to players steam profiles by default, also the ability to keep track of your own teammates availability is nice too.

As far as scheduling, this is mostly used to maintain 'quality' scrims instead of getting match up last minute scrims versus teams are are drastically better or worse than the skill level of your own team
7
#7
3 Frags +

I will happily use Discord for scrims if we got the large majority of Open teams / leaders to agree to that. Too often the channel is barren and completely unreliable.

I will happily use Discord for scrims if we got the large majority of Open teams / leaders to agree to that. Too often the channel is barren and completely unreliable.
8
#8
-1 Frags +

Don't think u can get away with just crediting this to sov in a spoiler tag!!!!!

SOV MADE MOST OF THIS DON'T BE FOOLED INTO GIVING +FRAGS TO THIS MAN!

Don't think u can get away with [i]just[/i] crediting this to sov in a spoiler tag!!!!!

SOV MADE MOST OF THIS DON'T BE FOOLED INTO GIVING +FRAGS TO THIS MAN!
9
#9
6 Frags +

Yes please give spreadsheet your names so I can immortalise all you gamers.

http://wiki.teamliquid.net/teamfortress/ESEA/Season_26/Open/North_America

Yes please give spreadsheet your names so I can immortalise all you gamers.

http://wiki.teamliquid.net/teamfortress/ESEA/Season_26/Open/North_America
10
#10
2 Frags +

Updated a slight formatting fix for discoloration of cells when the scheduled day & time blocks are "today". The Day cells are post-fix, and the time blocks are pre-fix (you can view the final adjustment using the same link to the spreadsheet in OP) (Image)

Show Content
To fix this on your own spreadsheet, under schedule sheet, select cell B1, right click and select conditional formatting. Use this as the cell range "B1,B5,B14,B23,B32,B41,B50,B59,B68,B77", then you want to format cells using the following formula "=TODAY()=INDIRECT("A"&ROW())". You can set the colors as your preference but I'll be using a vibrant green background with white bolded text on the master.

To fix the A column, right click cell A1 and select conditional formatting and use this range "A1,A5,A14,A14,A23,A32,A41,A50,A59,A68,A77", and you want to format the cells using "date is" and "today", then set the colors/formatting to your preference.
Updated a slight formatting fix for discoloration of cells when the scheduled day & time blocks are "today". The Day cells are post-fix, and the time blocks are pre-fix (you can view the final adjustment using the same link to the spreadsheet in OP) ([url=https://puu.sh/xI6eE/0922e8c271.png]Image[/url])

[spoiler]
To fix this on your own spreadsheet, under schedule sheet, select cell B1, right click and select conditional formatting. Use this as the cell range "B1,B5,B14,B23,B32,B41,B50,B59,B68,B77", then you want to format cells using the following formula "=TODAY()=INDIRECT("A"&ROW())". You can set the colors as your preference but I'll be using a vibrant green background with white bolded text on the master.

To fix the A column, right click cell A1 and select conditional formatting and use this range "A1,A5,A14,A14,A23,A32,A41,A50,A59,A68,A77", and you want to format the cells using "date is" and "today", then set the colors/formatting to your preference.
[/spoiler]
11
#11
3 Frags +

Change Log
Version 1.1
-Added "Next Week" schedule to ease the burden of having to reuse the same sheet
-Added another scheduling slot to both sheets as well as the quick guide
-Corrected some additional formatting/color errors
---Red is past dates, Green is current day, and Blue is future dates
---Added bold typeface in some areas to improve readability
-Updated map versions to those that will be used by ESEA

Change Log
Version 1.1
-Added "Next Week" schedule to ease the burden of having to reuse the same sheet
-Added another scheduling slot to both sheets as well as the quick guide
-Corrected some additional formatting/color errors
---Red is past dates, Green is current day, and Blue is future dates
---Added bold typeface in some areas to improve readability
-Updated map versions to those that will be used by ESEA
Please sign in through STEAM to post a comment.