Home Ready Room

Do Not Airlock Checklist Thread 4

cmdrworfcmdrworf ✭✭✭✭✭
edited March 27 in Ready Room
About the Do Not Airlock Checklist
The 'Do Not Airlock Checklist' is a Google Sheet that was originally created by TheAlchemyst. It was then added onto by several other people, the latest of which is yours truly and @mr_sparky . I have made some modifications and updates since that I hope will be of some use, and regularly add the new crew that keeps appearing in the game for some reason.

This thread follows on from three earlier threads on the previous forums. It is therefore numbered four.

Great. So what does it actually do?
The 'Do Not Airlock Checklist' can help you determine what crew you still need (or don't) in planning your crew, help you with tricky Beholds, or help you with a shortage of crew slots by telling you what crew you can murder by pushing them out of an airlock into space. You monster.

The sheet takes into account mission requirements, the crew with the highest minimum and maximum stats, cadet challenges, collections, voyages, and usefulness in the Gauntlet. Every time you mark a crew member as Active, the sheet removes the requirements that he or she meets from all other crew, gradually whittling down the usefulness of crew that you do not yet have.

Why did you have me airlock this crew that I now need?! (or: the Big Fat Disclaimer)
● The sheet uses the stats of all crew at level 100 and fully equipped (they do not need to be fully fused). Recommendations will be incorrect if you mark crew that you are still leveling and equipping as Active.
● The sheet does not take into account how difficult characters are to equip.
● The sheet does not take events or the Arena into account and how useful a character may be for that.
● Changes may (and do) occur to the game at any time, which may invalidate the recommendations that the sheet gives.
● Always use your own judgement and the Big Book of Behold Advice before airlocking any crew.

Most Current Version: (03/27 Version 6.26)
https://docs.google.com/spreadsheets/d/1-6Kt3Z20QXa34R35CB1RCwO4dR4tLttsXkTIAZnwG4Q/edit?usp=sharing

Conversion Helper
To help you more easily move your data from an older version 2.x to the new version 3.x, a conversion sheet is available: Do Not Airlock Conversion Sheet

How JiveDutch upgrades his sheet
Note that as of version 3.50, a semi-automated fill-in process is also available. See the Introduction tab in the sheet for more details.
Sir, I protest! I am NOT a merry man!
«13456760

Comments

  • cmdrworfcmdrworf ✭✭✭✭✭
    @Shan Can you remove the old sticky for this thread and sticky this one instead.

    Thanks.
    Sir, I protest! I am NOT a merry man!
  • Just did a test run ... seems to still work properly .... used an older datacore export then a new one to check updating of the data .. which worked properly and most of my "conversion" steps are still valid.

    Steps for quick transferring of settings & notes between sheet versions , this order should provide the fastest calculation order time
    1. Move my old one to diff folder and make a copy of the updated sheet on my G-Drive

    2.1 Missions Tab, multi-select and copy the Victorious Factions, and Ctrl-Shift-V (values only) over to the new sheet and display the proper checkboxes.
    2.2 Multi Select + spacebar makes quick work of all the missions ... otherwise paste values only is your friend again.

    3.1 Settings Tab, multi-select 3x crew, space bar to turn these calculations off
    3.2 Change minimum crew rarity settings for event and voyages settings ( i set to 4 )
    3.3 Copy the Starbase and Collection percentages (Ctrl+C), and Ctrl+Shift+V to paste them over as "Values Only"

    4.1 Crew Tab , unfilter all collumns to default view, sort by name A-Z, then Version Z-A on both sheets,
    4.2 multi-select Notes cells, and Ctrl+Shift+V to the new sheet. (i always check if the top and bottom notes align .. manual changes are required when crew names change and alter positions)

    5. re-apply your filter preferences and you are ready to go ....

    The following extra steps are used when you alter the sheet for manual Active/Fusion tracking.
    4.3 multi-select Active/Keep/Notes cells, and Ctrl+Shift+V to the new sheet. (i always check if the top and bottom notes align .. manual changes are required when crew names change and alter positions)
    4.4 multi-select all cells in Row B ( Fusion #) and paste them over ...
  • cmdrworfcmdrworf ✭✭✭✭✭
    Update posted. 3.52 is live.

    "Added New Crew: Lt. Ilia, Arjin, Age of Sail Worf
    Updated stats to auto calculate the 2*3*4* stats on 5* crew, and 2*3* stats on 4* crew and 2* on 3* crew"
    Sir, I protest! I am NOT a merry man!
  • Thank you @cmdrworf , this is very much appreciated!
  • BurrakookaBurrakooka ✭✭
    edited February 2020
    Hi @cmdrworf , looks like Maras has a static "4" in her column D on the Crew tab, rather than =Stats!$B524 which would bring up the correct "5". There are a few other crew around her that also have static values, not sure if there are more, these just happen to be nearby to Maras when I'm sorted by name and version: Fugitive Burnham, Stranded Craft, Kaminar Saru, Protomorphosis Riker, Rigel VII Vina.
  • cmdrworfcmdrworf ✭✭✭✭✭
    Burrakooka wrote: »
    Hi @cmdrworf , looks like Maras has a static "4" in her column D on the Crew tab, rather than =Stats!$B524 which would bring up the correct "5". There are a few other crew around her that also have static values, not sure if there are more, these just happen to be nearby to Maras when I'm sorted by name and version: Fugitive Burnham, Stranded Craft, Kaminar Saru, Protomorphosis Riker, Rigel VII Vina.

    @Burrakooka Thank you. Stealth fixed on the published and my master.
    Sir, I protest! I am NOT a merry man!
  • Thanks @cmdrworf for working on this :)

    Just for reference I want to put up @Leshy 's guide to update crew manually here too - I have two sheets for my SSR and Vip0 accounts, and copying over everything in settings, missions for vip0, notes etc. has always a bit tedious.
    1. Unhide the Stats sheet
    2. Add a row where the new crew should alphabetically go
    3. Fill in the name, details, and stats, all the way up to and including the columns Traits, (Part) Alien, and Female. This should be pretty clear.
    4. Copy the formulas in the columns AX–NY down from the row above (select AX–NY in the row above, click the little bottom right corner of the selection and drag it down to the new empty row).
    5. On the Crew sheet, add a new row in the same place where the new crew should go. Copy columns A–D and I–N down in the same way. Add in a new version number for the new crew.
    6. (Optional) Hide the Stats sheet.

    That should be pretty much all you need to do to add a new crew member to the sheet.

    Now I copy new crew from @cmdrworf 's sheet into the stats sheet and insert a line in the crew sheet where I copy the line above. Works fine so far for me :smile:
  • cmdrworfcmdrworf ✭✭✭✭✭
    Thanks @cmdrworf for working on this :)

    Just for reference I want to put up @Leshy 's guide to update crew manually here too - I have two sheets for my SSR and Vip0 accounts, and copying over everything in settings, missions for vip0, notes etc. has always a bit tedious.
    1. Unhide the Stats sheet
    2. Add a row where the new crew should alphabetically go
    3. Fill in the name, details, and stats, all the way up to and including the columns Traits, (Part) Alien, and Female. This should be pretty clear.
    4. Copy the formulas in the columns AX–NY down from the row above (select AX–NY in the row above, click the little bottom right corner of the selection and drag it down to the new empty row).
    5. On the Crew sheet, add a new row in the same place where the new crew should go. Copy columns A–D and I–N down in the same way. Add in a new version number for the new crew.
    6. (Optional) Hide the Stats sheet.

    That should be pretty much all you need to do to add a new crew member to the sheet.

    Now I copy new crew from @cmdrworf 's sheet into the stats sheet and insert a line in the crew sheet where I copy the line above. Works fine so far for me :smile:

    @/SSR/ Tichy One more thing on this. On the stats sheet there are formulas in the 2*, 3*, 4* stat coulumns for 5* crew, 2*, 3* for 4* crew, and 2* for 3 * crew that need to be copied from another crew with the same rarity so all of the * level stats update as well.
    Sir, I protest! I am NOT a merry man!
  • cmdrworf wrote: »
    Thanks @cmdrworf for working on this :)

    Just for reference I want to put up @Leshy 's guide to update crew manually here too - I have two sheets for my SSR and Vip0 accounts, and copying over everything in settings, missions for vip0, notes etc. has always a bit tedious.
    1. Unhide the Stats sheet
    2. Add a row where the new crew should alphabetically go
    3. Fill in the name, details, and stats, all the way up to and including the columns Traits, (Part) Alien, and Female. This should be pretty clear.
    4. Copy the formulas in the columns AX–NY down from the row above (select AX–NY in the row above, click the little bottom right corner of the selection and drag it down to the new empty row).
    5. On the Crew sheet, add a new row in the same place where the new crew should go. Copy columns A–D and I–N down in the same way. Add in a new version number for the new crew.
    6. (Optional) Hide the Stats sheet.

    That should be pretty much all you need to do to add a new crew member to the sheet.

    Now I copy new crew from @cmdrworf 's sheet into the stats sheet and insert a line in the crew sheet where I copy the line above. Works fine so far for me :smile:

    @/SSR/ Tichy One more thing on this. On the stats sheet there are formulas in the 2*, 3*, 4* stat coulumns for 5* crew, 2*, 3* for 4* crew, and 2* for 3 * crew that need to be copied from another crew with the same rarity so all of the * level stats update as well.

    Well I assumed since I'm copying the stats line of your new sheet (let's say line 29 for AoS Worf) the formulas are in there already?

    Just checked, the columns F..H contain a formula but no result. It seems I should migrate to the latest sheet and do manual updates from there.
  • cmdrworfcmdrworf ✭✭✭✭✭
    edited February 2020
    Correct. formulas were improved between 3.51 and 3.52 to make it smoother for adding new value pairs.

    Otherwise yes, you could copy paste from my posted sheet for all the formulas.
    Sir, I protest! I am NOT a merry man!
  • So, dumb question ... Does the player.json file not have the data on what missions we've completed? If so, couldn't that be imported as well?
  • USS Dart wrote: »
    So, dumb question ... Does the player.json file not have the data on what missions we've completed? If so, couldn't that be imported as well?

    It doesn't include the completed missions, but it does include the episodes' completed stars and total stars. So at least if for one episode the completed stars is equal to the total stars you can count all missions in that episode complete.

    However, that information cannot be exported in the CSV; a CSV by definition includes a single set of data (a single table). You need a more complex file format (such as an excel file with multiple sheets) to export multiple sets of data; that would be too difficult to import in Google Sheets though.
  • @TemporalAgent7 Thanks so much for clearing that up for me. On a related note, is there a way to update the sheet once you have done the datacore import? Right now, I'm deleting the sheet and starting over if I level a character.
  • As a general comment, it's nice to see so much of the community interested in a project like this. It's good to see a new influx of interest in these extremely valuable tools that people work so hard to put together for us.
  • Sven LundgrenSven Lundgren ✭✭✭✭✭
    USS Dart wrote: »
    @TemporalAgent7 Thanks so much for clearing that up for me. On a related note, is there a way to update the sheet once you have done the datacore import? Right now, I'm deleting the sheet and starting over if I level a character.

    You can update the import tab manually.
  • DeeMentiaDeeMentia ✭✭
    edited February 2020
    THANK YOU SO MUCH @cmdrworf for stepping up to the plate and doing this! Seriously appreciated.
    I do have an issue - My Twilight T'Pol is lvl 100 1/5*. On the import sheet it shows that but she doesn't show on the crew sheet
    Twilight T'Pol TRUE T'Pol 5 1 100

    Any clue

    EDIT:
    Its that naggy ' So the hypertext link has the squirrely ' but its supposed to be the straight '
    Not sure how to fix that on my sheet.

    Edit2:
    I fixed on my end by unhiding the Stats sheet and addressing that apostrophe there. It fixed the sheet for me.
  • New Note: It shows my level 1 crew as active (tech. true). But the sheet originally was to track level 100 (not necessarily fused) crew. That was oddly helpful to me because then I could see who I needed to level. Now my level 1 dude shows the same as my level 100 dude.

    Currently running a manually updated sheet and a imported one to see differences.
  • @cmdrworf For those not using the import, Xindi 'Prisoner' Archer, and Xindi Insectoid Councilor's sheet version (column H) have been swapped on the current Checklist. That will swap their stars and ticks on a straight copy & paste.
  • @cmdrworf I mentioned this on the original thread and it appears to have gotten missed in the shuffle of the change-over from Leshy to you. Douglas Pabst isn't registering as a variant of Odo on the sheet, even though every event I've experienced, he's been counted as an Odo. This is a parallel to Herbert Rossoff counting as a Quark and Benny Russell counting as a Sisko, which ARE correct on the sheet.

    I've corrected it on my own sheet, but I thought I'd point it out here as well for everyone else.
  • SpukkZSpukkZ ✭✭✭
    DeeMentia wrote: »
    New Note: It shows my level 1 crew as active (tech. true). But the sheet originally was to track level 100 (not necessarily fused) crew. That was oddly helpful to me because then I could see who I needed to level. Now my level 1 dude shows the same as my level 100 dude.

    Currently running a manually updated sheet and a imported one to see differences.

    Hi @DeeMentia! This is a side-effect of the import functionality added by Leshy just before they stopped maintaining the sheet. We're currently working on implementing some changes so that people can choose if non-FE people should be counted as active or not.


    Burrakooka wrote: »
    @cmdrworf For those not using the import, Xindi 'Prisoner' Archer, and Xindi Insectoid Councilor's sheet version (column H) have been swapped on the current Checklist. That will swap their stars and ticks on a straight copy & paste.
    @cmdrworf I mentioned this on the original thread and it appears to have gotten missed in the shuffle of the change-over from Leshy to you. Douglas Pabst isn't registering as a variant of Odo on the sheet, even though every event I've experienced, he's been counted as an Odo. This is a parallel to Herbert Rossoff counting as a Quark and Benny Russell counting as a Sisko, which ARE correct on the sheet.

    I've corrected it on my own sheet, but I thought I'd point it out here as well for everyone else.

    I've fixed both of these points in the Dev version of the checklist. They should be taken into account with the new version that @cmdrworf will release this week containing the new crew that'll be added for the next event and Tuesday packs.
  • Captain IdolCaptain Idol ✭✭✭✭✭
    Just stopping in to say stunning work you guys are doing here! Keep it up!
  • @SpukkZ Thanks for the fixes. Another one to maybe look at is Gary Seven being caught up in Seven (of Nine) event crew
  • cmdrworfcmdrworf ✭✭✭✭✭
    DeeMentia wrote: »
    THANK YOU SO MUCH @cmdrworf for stepping up to the plate and doing this! Seriously appreciated.
    I do have an issue - My Twilight T'Pol is lvl 100 1/5*. On the import sheet it shows that but she doesn't show on the crew sheet
    Twilight T'Pol TRUE T'Pol 5 1 100

    Any clue

    EDIT:
    Its that naggy ' So the hypertext link has the squirrely ' but its supposed to be the straight '
    Not sure how to fix that on my sheet.

    Edit2:
    I fixed on my end by unhiding the Stats sheet and addressing that apostrophe there. It fixed the sheet for me.

    Fixed in 3.53 which will be published this week. Thanks @DeeMentia And your quite welcome, but also I also need to thank @SpukkZ as he's working with me on this.
    Sir, I protest! I am NOT a merry man!
  • cmdrworfcmdrworf ✭✭✭✭✭
    Burrakooka wrote: »
    @SpukkZ Thanks for the fixes. Another one to maybe look at is Gary Seven being caught up in Seven (of Nine) event crew

    @Burrakooka Gary Seven is fixed. will be in 3.53 this week.
    Sir, I protest! I am NOT a merry man!
  • cmdrworfcmdrworf ✭✭✭✭✭
    Version 3.53 is out with updates/fixes/and new crew from today added.
    Sir, I protest! I am NOT a merry man!
  • cmdrworf wrote: »
    Version 3.53 is out with updates/fixes/and new crew from today added.

    Hey @cmdrworf , bug report on 3.53 is that after import the only crew showing as either need or active is Dark Ages McCoy. All others are showing as Space.

    Worth looking into for when the new crew are added for the final part of this mega?
  • PhilJS89 wrote: »
    cmdrworf wrote: »
    Version 3.53 is out with updates/fixes/and new crew from today added.

    Hey @cmdrworf , bug report on 3.53 is that after import the only crew showing as either need or active is Dark Ages McCoy. All others are showing as Space.

    Worth looking into for when the new crew are added for the final part of this mega?

    I think the problem is, on the hidden Stats tab, a lot of formulas aren't "copied down"...from AX5 to BI, and BJ5 to NY. If you know what I'm talking about, you can just copy all the formulas to the bottom of the tab and that looks like it fixes the problem.

    Otherwise, you should probably stick to 3.52 until there's a fix.
  • And @cmdrworf @SpukkZ @Leshy and everyone else who've contributed to this...

    I've been using this for a long time and this is the first time I've really looked at all the formulas involved. WOW! THANK YOU for all the work! This is one *really* impressive spreadsheet!
  • MururoaMururoa ✭✭✭
    edited February 2020
    Hello,
    Here the sheet 3.53 dont calculate anything.
    I have 'dark ages' mc coy in "Need" and all other in "space" even frozen crew. This first line is ok with all columns correct. No "Active", no "Frozen" and no "Space/Freeze" in other lines.
    Columns b and e are filled correctly but nothing in skills, recommandation and reason.
    It looks like the first line was calculated and then anything else.
    Just made a copy and import.
    Same imported sheet works in 3.50 outside Twilight T'Pol.

    Edit : just read the whole thread and the problem is known so. Where can I find 3.52 ?
  • SpukkZSpukkZ ✭✭✭
    PhilJS89 wrote: »
    cmdrworf wrote: »
    Version 3.53 is out with updates/fixes/and new crew from today added.

    Hey @cmdrworf , bug report on 3.53 is that after import the only crew showing as either need or active is Dark Ages McCoy. All others are showing as Space.

    Worth looking into for when the new crew are added for the final part of this mega?

    I've published a new version of 3.53 that fixes this. As @mr_sparky noted, something went wrong with the release and the formulas weren't copied to all crew.

    You can access the new version at https://docs.google.com/spreadsheets/d/1g2IIPOBWC3cUkN9d9NXe_usozYxJqOGW-xI_8ZvqVxs/edit?usp=sharing

    I can't update the main post myself, so we need to wait for @cmdrworf for that.
Sign In or Register to comment.