MrPotatoTed Posted November 1, 2021 Share Posted November 1, 2021 @vcczar @DakotaHale @ConservativeElector2 @Cal @jvikings1 @Hestia @Rezi @Rodja Hi guys -- I've been working to automate the excel sheet wherever possible. My excel skills are nil, I have no training -- but I'm teaching myself how to do it. My hope is that successfully automating so many functions in the excel sheet will make even the most complicated processes such as the legislative and elections super easy for all of us. Less work for you, less work for me, and therefore less burnout and more focusing on just having fun and providing useful feedback for the playtest. To that end, this will be a running progress report on what I've taught myself how to do, what I've achieved with those new skills, and what I still want/need to do. Last night, I taught myself the SUM function and its many uses. That's probably Excel 101 for all of you, but I never took Excel 101 so that's where I started. After successfully getting that done, I taught myself how to calculate and pull Sums from faction pages and display that information on different pages (such as how many times the word "Senator" or "Rep" appears on your politicians' job titles. That was a major hurdle that I'm super excited to have figured out, and will be very useful. My biggest remaining hurdle that will make the entire game 100% easier for everybody is figuring out how to create a drop-down list for politician names (where typing the first few letters will give you the politician's name) and then it autopopulates the relevant data on the sheet, like whether they have traits that impact their election odds. I don't think I'm ready for that level yet though, so I'm going to finetune my skills first by automating everything that I can on every page. Step one: I'll complete all of the "low hanging fruit" (the items I now already know how to do or can easily learn now). Step two: I'll learn how and then implement the "high effort/high reward" stuff that will save us huge amounts of time like the drop down thing where we won't have to keep looking up data for each individual politician anymore. Step three: Automating the rest of it -- stuff that won't necessarily save us significant time or be easy to do, but by the time I complete step two, I should have the skills to knock it out. Progress so far: MAIN TAB - Complete: Automatically generating # of Senate and House votes for each faction, automatically generating # of Senate and House votes for each party. - Short term To-Do: Automatically generate # of Governors for each faction and party (I know how to do this, will complete it momentarily. - Long term To-Do (Step Three): Automatically generate the ideologies, special interests, and lobbies. 4 Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 1, 2021 Author Share Posted November 1, 2021 MAIN TAB: Complete Automatically generating Senate count for each faction Automatically generating House count for each faction Automatically tallying Senate count for each party Automatically tallying House count for each facion Locked the top two rows so the title of each column scrolls down when you scroll down Automatically generating Governor count for each faction (NOTE: This required standarizing the job title of those on career tracks to just "Career Track" instead of things like "Career Track Governing" which was leading the formula to believe those were actually Governors. I've corrected this on all faction sheets). Automatically tallying Governor count for each party TO DO (Phase Three) Automatically generate the lobbies, ideologies, and interests for each faction BIG SMALL STATES TAB: (skipped, doesn't seem like anything here needs automated) FACTION NICKNAMES: (Skipped, doesn't seem like anything here needs automated) FACTION NUMBER TAB: (Skipped, doesn't seem anything here needs automated) LEGISLATION ACTIVE TAB: TO DO (Phase Three) Not really an automation task, but I want to clean this up and only show the information that is actually relevant to players/me -- what can be repealed, and/or major laws we need to know at a glance have been implemented. Get rid of the rest. LEGISLATION PROP TAB: Complete Automatically replaced all references to the outdated "Big Prison" with the new "Law and Order". I can continue to do this for any other outdated terms that we spot, that's just the only one that lept out at me. Automatically replaced all stand-alone "0"s in the lingering columns with blank spaces (while making sure it didn't screw up things like "10" or "100") so we can more easily see at a glance which laws have an impact on the meters TO DO (Phase Three) Can I create a formula to automatically calculate point totals from passed legislation based on what cards a faction has? SCRIPTED EVENTS TAB: (Skipped, doesn't seem anything here needs automated) LINGERING TAB: Complete Automatically replaced all stand-alone "0"s in the lingering columns with blank spaces (while making sure it didn't screw up things like "10" or "100") so we immediately spot which laws in the lingering tab have no actual lingering relevance at all Deleted all laws in this tab that had no actual lingering relevance at all CARDS HELD TAB: Complete Manually hid all cards that aren't allowed to exist in this era. (I'll need to manually update this each time the era changes) TO DO (Phase Three) Can I create a formula that would automatically flag when a faction gets incompatible cards? Can I create a formula that would automatically generate the correct ideologies/lobbies/interests to the right faction on the main tab? METERS TAB: Complete Cleaned up this tab, removed irrelevant/old restrictions on the right hand side, moved restrictions that were in the wrong spot, deleted duplicated restrictions, scrolling down works a lot better on the tab now Manually updated the Meter# for each data point TO DO (Phase Three) Can I create a formula to automatically update the Meter#? Or, failing that, maybe remove Meter# altogether as I don't think players actually use it? That might be it for today, but solid progress. Other than teaching myself the basics for about an hour last night, all of this only took me a total of about an hour today. Will pick it up again tomorrow. 1 2 Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 3, 2021 Author Share Posted November 3, 2021 I think that I've maaaaaaybe found a video that might teach me enough for me to create a way to get the elections to work in the way that we want them to (minimal work for me, no work for anyone else other than choosing your candidate). It won't work exactly the way the video shows...but if I "hide" dead/retired politicians instead of deleting them, maaaaaybe it would work. Maybe. I hope. Ha. 3 Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 3, 2021 Author Share Posted November 3, 2021 CAREER TRACK: Complete: I've figured out how to create drop-down lists! Right now, these are present in the Career Track. Each faction's section on the career track has a drop down menu containing only their current statesmen. As people fall off/get added to factions, they should automatically fall off/get added to the drop-down lists. Starting to type the name of a person in that faction (first or last name) will make them appear in the career track -- no need to type the whole name. TO DO: (Phase 3) Can I create a formula that either automatically adds someone to the career track if you give them the career track in their career -- or make it show up as their career if you add them to the career track? 1 Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 3, 2021 Author Share Posted November 3, 2021 ALL FACTION TABS: COMPLETE: Figured out why dynasty, politician parents and spouses, special draft indicator, and Wiki pages weren't being sorted correctly when we sort columns (which is why the Wikipedia pages don't match the right politicans right now) and fixed it! Note: each item would have to be fixed manually right now for all current politicians and I'm not going to bother with that -- but all future politicians with have these columns sort correctly so that it won't be a problem anymore Added a new column on the faction sheet that automatically calculates everyone's age by what 2-year term we're in. This will really help with deaths, retirements, and confirming everyone is the right age to run for office. (When I get to the draft page, I'll add this column as well) 1 Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 3, 2021 Author Share Posted November 3, 2021 @vcczar I'm having an issue creating the age column for some of the factions. Specifically, Dakota, SilentCPU, and Conservative. I took a look and I think the problem is those sheets are protected -- I can edit "most" of the things on them, but not the names of the columns (A row). I tried following the instructions here, but I couldn't get to the page where I could delete the instruction because I'm not the owner. Can you try following this?https://support.google.com/docs/thread/14761972/how-to-prevent-sheets-from-being-deleted-by-other-editors 1 Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 3, 2021 Author Share Posted November 3, 2021 2 minutes ago, MrPotatoTed said: @vcczar I'm having an issue creating the age column for some of the factions. Specifically, Dakota, SilentCPU, and Conservative. I took a look and I think the problem is those sheets are protected -- I can edit "most" of the things on them, but not the names of the columns (A row). I tried following the instructions here, but I couldn't get to the page where I could delete the instruction because I'm not the owner. Can you try following this?https://support.google.com/docs/thread/14761972/how-to-prevent-sheets-from-being-deleted-by-other-editors @vcczar Disregard. I think I can fix it by just duplicating the sheet and erasing the old one. 1 Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 3, 2021 Author Share Posted November 3, 2021 @vcczar can you delete the main Dakota Hale, ConserveElector, and SilentLiberty sheets? (Not the duplicates) 1 Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 3, 2021 Author Share Posted November 3, 2021 DRAFT: Added Age column so new politicians will be aligned correctly. 1 Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 3, 2021 Author Share Posted November 3, 2021 HISTORY TAB (I'm fairly confident some of this could be automated...but not in the way that I want it to look. So I'm going to leave this as-is for now. With any luck, updating this tab will become the only time-consuming part of this game moving forward). GOV ACTIONS COMPLETE: Created a filter so we can easily sort by era Filtered out all irrelevant actions (by era) so that you can only see the things you can currently do 1 Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 4, 2021 Author Share Posted November 4, 2021 INDUSTRY TAB: COMPLETE: Adding a note at the top left explaining the color scheme (I hope -- I'm actually colorblind, so I made my best guess at what the colors were. ha.) GOVSTATES TAB: TO DO: I want to redesign this page to include the relevant skills for Governors (that influence Governor actions). But I'll wait until I get to Phase Three. I've now reached the Executive tab...if I can figure out how to automate this, I should be able to use those exact same skills and formulas to make the elections work. Here goes! 2 Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 4, 2021 Author Share Posted November 4, 2021 My research has confirmed what I already suspected: it's not possible to create a drop-down list that pulls names from multiple (faction) sheets. Not a problem. I've created a master sheet that now has all active politicians on it. I'll just add more names to it as people get drafted. I'm not sure yet whether it'll be better to manually delete statesmen from the master sheet as they die, or to leave them all up there. It will depend on how I get things like "integrity" to display when a politician's name is chosen from a drop down. 1 Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 4, 2021 Author Share Posted November 4, 2021 I FOUND IT! Instructions on how to get items to autopopulated based on dropdownlist selection! https://www.extendoffice.com/documents/excel/2400-excel-drop-down-list-auto-populate.html 1 1 Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 4, 2021 Author Share Posted November 4, 2021 I've got the drop down menus created for the executive tab -- one interesting problem I encountered is that I get an error if I try to create a drop down list with more than 1000 names. Right now we have 412 active politicians. That number will change over time of course, but as long as I delete those who die off or retire, I assume we'll always be below 1000 so it should be fine. 1 Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 4, 2021 Author Share Posted November 4, 2021 I've created a "Faction Name" column on each faction sheet and also the Master Sheet -- so that in addition to the relevant traits, it will also automatically pull the faction name when you choose a politician from the dropdown list. 1 Quote Link to comment Share on other sites More sharing options...
Popular Post MrPotatoTed Posted November 4, 2021 Author Popular Post Share Posted November 4, 2021 @vcczar @Hestia @Rezi @Rodja @Cal @ConservativeElector2 @DakotaHale @jvikings1 I DID IT!!! I've successfully managed to get items to autopopulate based on the name from a dropdown menu! You can see it in action on the Executive sheet -- right now it's just automatically pulling the faction name for each member of the executive branch, but it's the exact same formula that I can use for everything else. Including the elections! We're saved! Ha 2 4 Quote Link to comment Share on other sites More sharing options...
Hestia Posted November 4, 2021 Share Posted November 4, 2021 Just now, MrPotatoTed said: @vcczar @Hestia @Rezi @Rodja @Cal @ConservativeElector2 @DakotaHale @jvikings1 I DID IT!!! I've successfully managed to get items to autopopulate based on the name from a dropdown menu! You can see it in action on the Executive sheet -- right now it's just automatically pulling the faction name for each member of the executive branch, but it's the exact same formula that I can use for everything else. Including the elections! We're saved! Ha I could have never done that 😄 Your hard work is not ignored! Great work!!! 2 Quote Link to comment Share on other sites More sharing options...
vcczar Posted November 4, 2021 Share Posted November 4, 2021 1 minute ago, MrPotatoTed said: @vcczar @Hestia @Rezi @Rodja @Cal @ConservativeElector2 @DakotaHale @jvikings1 I DID IT!!! I've successfully managed to get items to autopopulate based on the name from a dropdown menu! You can see it in action on the Executive sheet -- right now it's just automatically pulling the faction name for each member of the executive branch, but it's the exact same formula that I can use for everything else. Including the elections! We're saved! Ha Exceptional work!!!!!!!!! 2 Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 4, 2021 Author Share Posted November 4, 2021 15 minutes ago, Hestia said: I could have never done that 😄 Your hard work is not ignored! Great work!!! Ha, thanks! Honestly, I thought I could never do it either. I mean, on Monday, I was just trying to teach myself how to get excel to add two numbers. Ha. But ADHD hyperfocus for the win! ;c) 3 1 Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 5, 2021 Author Share Posted November 5, 2021 Executive Tab is fully automated now. Typing a couple letters of the cabinet member's name will make them appear on the drop down list, and selecting them will generate all relevant traits. (Note: I haven't actually confirmed in the rules that we actually have all the relevant traits on the sheet yet, but I automated what was already there. For this and all other sheets, if we get to a section in the rules and I realize we need to add something to a sheet, it'll be an easy fix.) Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 5, 2021 Author Share Posted November 5, 2021 Redesigned the GovStates tab: Created drop down list for each state's governor. Faction name is automatically generated. Automatically checks for gov ability level, efficient, incompetent, Leadership, Iron Fist, Pliable, Puritan. Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 5, 2021 Author Share Posted November 5, 2021 Military Tab: Created the dropdown menu for each office. Automatically generates faction name, Military ability, and Military Leader. Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 5, 2021 Author Share Posted November 5, 2021 PolData: Fixed the formulas for our 3 factions that needed new tabs. Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 5, 2021 Author Share Posted November 5, 2021 CardsHeld Tab: Fixed the formulas for our 3 factions that needed new tabs Quote Link to comment Share on other sites More sharing options...
MrPotatoTed Posted November 5, 2021 Author Share Posted November 5, 2021 Main Tab: Fixed the formulas for our 3 factions that needed new tabs. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.