Do Not Airlock Checklist Thread 3: Press Ctrl-F, type Spock

1356730

Comments

  • LeshyLeshy ✭✭✭
    Fixed now, they're all visible on the 'Crew Members' sheet – thanks for the notice!
  • al103al103 ✭✭✭
    edited October 2017
    Leshy wrote: »
    DIP: Enterprise-D Picard + Dabo Girl Leeta / Ensign Sato + Dabo Girl Leeta / Gaia Odo + Ensign Sato
    List explicitely tell me to freeze Picard and Leeta, despite Sato and Odo being in freezer.

    More than that - it doesn't tell me to unfreeze another combination (only one with "cadet roster" is Pazlar who I do not plan to have in my crew like ever... even Dax is a big maybe in comparison to Seven/Saru (previously Seven/Spock)).

    PS. And it does NOT tell me to freeze Seven any more despite telling me to freeze Saru...
  • LeshyLeshy ✭✭✭
    Yes, if you have all four of them, whether Active or Frozen, the sheet will consider the Diplomacy requirement of the cadet challenges completed and none of them will have the Cadet Roster requirement anymore (which is why the sheet might tell you that two of them can be spaced). It is a very minor downside to the solution of getting all their combinations in correctly.

    As mentioned, the sheet goes by the previously mentioned list of best cadet crew. Requiring either Dax or Pazlar is more efficient than requiring both Seven and Saru or Seven and Spock, so I am okay with it not making that recommendation. Adding in all possible options for cadet combinations that work would mean adding a lot of calculations to the sheet, slowing it down, which I do not want to do unless absolutely necessary.
  • This has been an awesome and very helpful sheet for me! Thank you for all your hard work!

    I noticed that as I changed how the Crew Members sheet was organized (e.g. sorting by number of stars, then alphabetically), the "Owned" column in the Stats sheet broke because the match function is looking for a completely alphabetical name column. Subsequently, the Top 4 on the Data sheet doesn't have anything to pull from, though it seems like nothing else broke.

    I brute-forced a way around this for that "owned" column, though you might have a more elegant solution:
    =if(or(not(isblank(index('Crew Members'!$A:$C,match($A2,'Crew Members'!$A:$A,0),2))),not(isblank(index('Crew Members'!$A:$C,match($A2,'Crew Members'!$A:$A,0),3)))),"x","")

    Basically, this will force match to look through the entire column as if it wasn't sorted alphabetically. Then it'll check if any of the cryo or active columns in the Crew Members sheet aren't blank, then return like you originally wanted.

    Hopefully this is helpful!
  • al103al103 ✭✭✭
    Leshy wrote: »
    Yes, if you have all four of them, whether Active or Frozen
    Uhm, but list does nag me to unfreeze/keep active for other cadet challenges. Isn't it 2-stage check "if active keep, if freezed unfreez"?
  • LeshyLeshy ✭✭✭
    jgallen wrote: »
    Hopefully this is helpful!
    Thanks a lot for the feedback and the suggestion, it is indeed helpful. I have added it into the existing 1.75 version as a quick fix, and will be in future versions too!
    al103 wrote: »
    Uhm, but list does nag me to unfreeze/keep active for other cadet challenges. Isn't it 2-stage check "if active keep, if freezed unfreez"?
    The other skills operate differently.

    The sheet does a simple check, such as "If this crew member is Lt Tasha Yar, Major Kira, or Persis, you need this crew member for the Cadet Roster" (for a second Security cadet). A separate check then removes the requirement from the other ones when you get one of them.

    Since the Diplomacy crew requires one of several different and partially overlapping crew combinations, it cannot be done in the same way. It instead does a check like "If this crew member is Ensign Sato and you do not already have Dabo Girl Leeta and Enterprise-D Picard, you need this crew member for the Cadet Roster".

    This allows for the three mentioned possible combinations to be correctly evaluated and marked; however, if you have all four of them, the sheet will obviously consider all of them not needed (since you always have another eligible crew combination already). I think that is a fairly minor downside.
  • al103al103 ✭✭✭
    edited October 2017
    *looks on trait change list*

    Oh, wow! Good luck...
  • LeshyLeshy ✭✭✭
    edited October 2017
    al103 wrote: »
    Oh, wow! Good luck...
    Just a bit of checking of boxes, fortunately, nothing too complicated. Until they start fiddling with mission requirements at least...

    Version 1.76 is now ready to go, with the latest trait changes in. It results in the following challenges, for who is interested:

    Crew with trait changes not resulting in any change to mission capabilities:
    Age of Sail La Forge, Ambassador Spock, Angel I Riker, Assimilated Torres, Author Doctor, 
    B'Etor Duras, Beowulf Kim, Changeling Bashir, Chief Engineer Torres, Comm Officer Uhura, 
    Command Hologram, Commander Chakotay, Commander Spock, Commander Uhura, Commando Crusher, 
    Dancing Uhura, Detective Data, Dr. Reyga, Dr. Soong, Dr. Tolian Soran, Duelist Torres, 
    Emotion Chip Data, Ensign Kim, Expedition Vash, Festive Jadzia Dax, Gangster Spock, Grand 
    Nagus Zek, Hockey Kim, Hockey Paris, Holonovel Doctor, Kal-if-fee Spock, Laborer Spock, 
    Liaison Torres, Lon Suder, Lore, Lt Commander Data, Lt Jadzia Dax, Lursa Duras, Luther 
    Sloan, Maquis Takeover Torres, Mirror K'Ehleyr, Mirror Uhura, Mobile Doctor, Musketeer 
    La Forge, Nyota Uhura, Promoted Sisko, Prospero Data, Recovering Spock, Rogue Harry Kim, 
    Romulan Data, Security Chief Tuvok, Security Chief Worf, Silik, Talos IV Spock, Technician 
    Rom, Temporal Prisoner Chakotay, Tempted Data, The One, Lore, The President of Earth, 
    Warship Chakotay
    

    Crew with trait changes now able to pass additional missions:
    1701 Jadzia Dax             Lost Legacy (SCI Node)
    1701 Sisko                  Thy Enemy's Secrets (CMD Node), Smiles and Knives
    Agent Harris                Off at the Pass
    Arik Soong                  Pretense of Mercy
    Aviator Yar                 Off at the Pass, The Annihilation Syndrome (SEC Node)
    Bashir, Julian Bashir       Improvised Entry
    Blood Oath Jadzia Dax       Serious Business (SEC Node), Blunting the Dagger
    Boxer Chakotay              By Hook or by Crook
    Captain La Forge            Cytherian Highway
    Captain Sisko               A Time of Morn-ing
    Captain Sulu                Control Over Chaos
    Chancellor Gowron           A Time of Morn-ing
    Chief Engineer La Forge     Cytherian Highway
    Claiborne Chekov            Off at the Pass, The Annihilation Syndrome (SEC Node)
    Dark Ages McCoy             Smiles and Knives, Improvised Entry
    GI Chakotay                 By Hook or by Crook
    Hugh                        Mortals and Mayhem (DIP Node), Iconian Insider
    Idrin                       Picking the Bones
    KDF Gowron                  A Time of Morn-ing, By Hook or by Crook
    Klingon Bride Jadzia        Ishka Issues, Blunting the Dagger
    Lt. Ash Tyler               Striking at Shadows (SEC Node), Serious Business (SEC Node), 
                                The Raptor's Fury (SEC Node), Control Over Chaos, Blunting 
                                the Dagger, The Time Transporter (CMD Node)
    Lt Commander Jadzia Dax     Ishka Issues
    Lt Commander La Forge       Cytherian Highway
    Lt Tasha Yar                Off at the Pass
    Mirror Garak                Off at the Pass
    Mirror Intendant Kira       Thy Enemy's Secrets (SEC Node)
    Mirror Phlox                Thy Enemy's Secrets (SEC Node), Off at the Pass
    Princess Jadzia             Ishka Issues
    Resistance Shakaar          Striking at Shadows (CMD Node)
    Tailor Garak                Cardassian Hospitality
    Temporal Agent Seven        Lost Legacy (SCI Node)
    Third Remata'Klan           Picking the Bones
    Waitress Ezri               By Hook or by Crook
    Warship Yar                 Picking the Bones, Off at the Pass, The Annihilation Syndrome (SEC Node)
    Wrathful Khan               Thy Enemy's Secrets (SEC Node), Off at the Pass
    Young Khan                  Mortals and Mayhem (DIP Node), Iconian Insider
    

    Crew with trait changes no longer able to pass certain missions:
    Agent Harris                By Hook or by Crook
    Shinzon                     Trust Issues, Iconian Insider
    

    Crew with announced trait changes that appear to not be in game:
    Gabriel Bell, KDF Worf
    

    Edit: Great, apparently I have now not been around long enough to post links... bear with me while I figure out how to add the link back into the opening post.
  • al103al103 ✭✭✭
    edited October 2017
    Speaking of 1.75

    I wonder why it demand for me to freeze T'Kuvma

    As in even if I remove almost everybody from the list - which leaves Hollander and Kortar to compete with him for EIGHT slots of Com and EIGHT slots of Sec.

    docs.google.com/spreadsheets/d/1A_4nPn928Tc-3GRM2z6aXg5wZxgLpHx1Z6LmWwuc8r4


    EDIT: not important as 1.76 healed that...
  • IgelrockIgelrock ✭✭
    edited October 2017
    maybe i get this wrong
    if i set top crew to 1 - only put in my 3* crew in, it should only keep 1 for base and and 1 for max of SCI, right?
    but when i mark Dr Carol Marcus as Sci3* Max I should no net need Dr. Reyga anymore. but it still tells me i need - and vice versa
  • al103al103 ✭✭✭
    Igelrock wrote: »
    maybe i get this wrong
    if i set top crew to 1 - only put in my 3* crew in, it should only keep 1 for base and and 1 for max of SCI, right?
    but when i mark Dr Carol Marcus as Sci3* Max I should no net need Dr. Reyga anymore. but it still tells me i need - and vice versa
    I'm not sure, but...

    Aren't they both needed for Advanced Challenges this way? Fem Sci and Alien Sci.
  • LeshyLeshy ✭✭✭
    edited October 2017
    Igelrock wrote: »
    maybe i get this wrong
    if i set top crew to 1 - only put in my 3* crew in, it should only keep 1 for base and and 1 for max of SCI, right?
    but when i mark Dr Carol Marcus as Sci3* Max I should no net need Dr. Reyga anymore. but it still tells me i need - and vice versa
    While the two things have similar names, they are different criteria.

    The SCI 3* Base, SCI 3* Max (and so on) requirements indicate what characters are the best for that skill and rarity out of all characters available, regardless of whether you have them or not. The sheet basically says "Hey, you need Ba'ku Worf because he is one of the best 4* characters for Security."

    The setting for 'Top Crew for each Skill' indicates how many crew that you actually have should be kept for each skill. The sheet basically says "Hey, you should not airlock your Civil War Worf. You may not need him for anything and he is not the best 4* Security character that you can get, but he is one of the best Security guys that you have right now."
  • al103al103 ✭✭✭
    Leshy wrote: »
    While the two things have similar names, they are different criteria.
    And if Wiki and my calculations are to be trusted - Reyga for both. So if "keep 1" then Marcus should be dumped.
  • LeshyLeshy ✭✭✭
    al103 wrote: »
    And if Wiki and my calculations are to be trusted - Reyga for both. So if "keep 1" then Marcus should be dumped.
    The 'Base' requirements are not completed until you have three of those crew, the 'Max' requirements not until you have two of them.

    So only marking Dr. Reyga active will not remove the requirement from Dr. Carol Marcus. Having both Dr. Reyga and Lieutenant Saavik (for example) active will remove it. This is intended.
  • al103al103 ✭✭✭
    Leshy wrote: »
    The 'Base' requirements are not completed until you have three of those crew, the 'Max' requirements not until you have two of them.
    Then why there is 1 in options? If minimum is 2 anyway.
  • LeshyLeshy ✭✭✭
    al103 wrote: »
    Then why there is 1 in options? If minimum is 2 anyway.
    Please reread my previous post – they are two different criteria.

    The 'Base' and 'Max' requirements indicate which characters are the best for each skill out of all crew available, suggesting that you should get three of them for the 'Base' requirement, and two for the 'Max' requirement.

    The mentioned setting in 'Advanced Settings' controls how many of the best crew of your currently active roster you should keep even if they meet no other requirements (by default, four).
  • LeshyLeshy ✭✭✭
    And that's version 1.77 with the newest crew and the latest trait changes in!
  • thanks leshy :)
  • ThaiGQThaiGQ ✭✭✭
    Thanks so much for all the hard work, Leshy!

    I have a bit of a noob question... Is there a way to update/import the selections made from an older version to a newer one? Or is it necessary to manually input the data every time?
  • LeshyLeshy ✭✭✭
    Thanks, Leka and ThaiGQ!

    The easiest way of importing the data from an older sheet, is to sort the 'Crew Members' sheet in both the old sheet and new sheet by 'Version'. (Press the little arrow next to the header and select 'Sort A -> Z'.) It will place the newest crew all the way at the bottom of the sheet, which allows you to copy the 'Active' and 'Cryo'd' columns from the old sheet and paste them into the new one.

    I have just made a stealth change to the current 1.77 version. The header boxes on the 'Missions' tab are now ungrouped, so that you can select the entire range of 'Completed/Not Completed' cells and copy-paste them into the new sheet in a single go.

    Updating will require a bit of manual labour, but copy-pasting should take care of the bulk of it.
  • Thank you for that explanation, I was also meaning to ask that ^_^ Adding crew from the beginning every time was started to bother me :)

    Btw was there some change in last sheet how cadet crew is considered? It looks like that now I don't need anymore most of my crew for cadet missions like Ro and few other cadets,..but probably that was due new crew members that I have got recently.

    Also I wanted to ask you something, if you would find it useful to add, I have some crew members that I should space but I still keep them (for example Martok,you must have at least one drunk Klingon in your crew :D;) ) but at some point when my crew hits max number he will have to leave if I don't manage to level him to max to croy him. When some character you don't have is considered "Space" and you get him and put sigh that he is Active, Status field will get green with Active message so the only way to figure out is recommendation for him to be Spaced is to remove your sigh form Active cell and wait for Status cell to change,then repeat that for every other character. I mark such characters that are safe to space manually with yellow background on version cell.
    Would it be possible, that if we mark some character as Active that his Status field is colored Red/Pink with message Active indicating that character is safe to space or with green and message Active indicating that you Need that character for something.
  • LeshyLeshy ✭✭✭
    Showing the reason for crew remaining 'Active' is a request that has come up multiple times. However, the sheet basically works by NOT showing missions for which the requirements have been met.

    Getting them to show up again for active crew that can complete them only, is a bit tricky to do and can impact the performance of the sheet quite a bit. I have not yet found a good way of doing it, but it is definitely something on the radar.
  • Hm I'm not sure I explained well or that I understood right this part of sheet function...

    If I don't have Martok and I look at my sheet,it say that with my current crew, Martok is clear to be air locked when I get him:
    77zdmwa5fxcl.jpg

    I decide not to listen to sheet recommendation and I decide to keep Martok because he likes blood wine ^_^ and I put a mark x in active field,now his status change from Space to Active

    xe7w95kvw9a5.jpg

    So in future, when my list of crew gets to its max and I have to airlock somebody I wont know who had "Space" status before it changed to Active,unless I remove "x" and see if status change to Need or Space. If character had "Space" in status before it changed to Active,then such field should be marked like this:
    siycdv51mbku.jpg

    But its probably too much effort for something most people wont use, I'm sorry if I didn't understood your last answer and if I'm just wasting your time on something on which you probably answered multiple times :( And thanks again for great helping tool.
  • LeshyLeshy ✭✭✭
    Note that the sheet basically already does this :)

    stt-sheet-1.png

    The reason that General Martok remains 'Active' on your sheet, is likely because he is one of your best Security crew. The sheet will not recommend you to airlock (or freeze) him in that case, but suggests that you keep him active.

    This ties in with my previous comment – it would be useful if the sheet could show why crew is still marked as Active, but this is not a hugely easy thing to accomplish in an efficient way.
  • Aaaaah...so even if sheet say space for some new character which I dont have I should still mark it as active when I get him to see will it suggest active or space/freeze.Thanks for that clarification :) (btw I know that sheet is considering characters as on their max lvl but I still put them in even if they aren't so I know that I'm going in right direction)
  • LeshyLeshy ✭✭✭
    Please keep in mind that when you put in non-maxed crew that the sheet will think that you can complete a number of missions which you may in reality not be able to complete (yet). It can then tell you to airlock other crew that could also pass those missions.
  • I tried a small fix for the problem where the sheet recommends freezing DIP cadets when it shouldn't. In my case I have Picard and Odo frozen and Leeta and Sato active, so the sheet recommends freezing/spacing leeta and sato. I changed for the leeta column from


    =if($A5="Dabo Girl Leeta",if(and(index($B:$B,match("Ensign Sato",$A:$A,0))<>"",index($B:$B,match("Gaia Odo",$A:$A,0))<>""),"","x"),"")

    to

    =if($A5="Dabo Girl Leeta",if(and(index($B:$B,match("Ensign Sato",$A:$A,0))="x",index($B:$B,match("Gaia Odo",$A:$A,0))="x"),"","x"),"")


    What I did was change checking for empty cells (<>"") to checking for cells with an x (="x").

    As I see it this change the meaning of the formula from "consider Leeta necessary unless you have Sato and Odo" to
    "consider Leeta necessary unless you have Sato and Odo *ACTIVE*".




  • al103al103 ✭✭✭
    Leka wrote: »
    Aaaaah...so even if sheet say space for some new character which I dont have I should still mark it as active when I get him to see will it suggest active or space/freeze.Thanks for that clarification :)
    It's more like sheet says that this character is not worth leveling, but worth to keep if already leveled.
  • No suggestions other than to say thanks so much for the work you do on this sheet. It's been absolutely invaluable, and has added a lot of fun to the game by feeling like i can make more educated decisions. :) (Plus I love spreadsheets.)
    Ten Forward Loungers - Give Your Best, Get Our Best!
    Check out our website to find out more:
    http://tenforwardloungers.freecluster.eu
  • LeshyLeshy ✭✭✭
    I tried a small fix for the problem where the sheet recommends freezing DIP cadets when it shouldn't.
    Thanks a lot, seems like a good suggestion. Included it in 1.80! :)
    Amy B wrote: »
    (Plus I love spreadsheets.)
    So do I, I just never admit to it in public. Thanks ;)

    I have now also released version 1.80. I suddenly had a simple idea of how to include the requirements for Active crew without it being a large performance hit, and it seems to work fairly well. Let me know if you run into any issues with it!
Sign In or Register to comment.