I really like this sheet but have a difficult time keeping it current with my actual crew. So I decided to forgo manual data entry and use the DataCore crew.csv file. I added a tab to the DNA Checklist that can be used to import the DataCore file, at which point the Crew tab is automatically updated.
In addition to changing the Level and Active columns on the Crew tab, I made a couple of other small edits as well:
Updated the instructions
Added a few more stats near the top of the Introduction tab
Added an Immortal column to the Crew tab
Changed the highlighting of the Name column on the Crew tab to reflect immortalization rather than just fully fused
It is quite a bit slower than Leshy's unmodified sheet, but there is a lot of duplicate info in the new tabs and the existing ones; I didn't want to do major surgery to the original sheet so it would be easier to replicate this in the future, but it should be possible to speed things up by getting rid of duplication.
I don't contribute enough to the forums to be allowed to post a link, but prefix the following with https:// and you can see what I've attempted here: docs.google.com/spreadsheets/d/1-wjztyIa-eLcxGAtWYJoKkgL6cwFN6C1ATiZ4kp4rCk/edit?usp=sharing
Comments welcome!
Thanks again to Leshy for the existing DNA Checklist, to the DataCore folks for processing the raw DisruptorBeam data into something coherent, and to Captain Idol for giving me the idea to import the DataCore file.
Once you have logged into stt.disruptorbeam.com/player and gotten your game data, there doesn't seem to be any way to actually LOG OUT! If you go to stt.disruptorbeam.com the game has an annoying habit of jumping to the Facebook login screen, which doesn't help if you do not / or do not want to use Facebook to log into the game.
The work around solution that I have found, is to jump to games.disruptorbeam.com where there is a LOGOUT screen. Then you will need to clear your cache and log in again, so that the game data is downloaded again, instead of just refreshing from cache. I've found the process to be a bit picky, so it may work for you to just try a different browser for the process.
I really like this sheet but have a difficult time keeping it current with my actual crew. So I decided to forgo manual data entry and use the DataCore crew.csv file. I added a tab to the DNA Checklist that can be used to import the DataCore file, at which point the Crew tab is automatically updated.
In addition to changing the Level and Active columns on the Crew tab, I made a couple of other small edits as well:
Updated the instructions
Added a few more stats near the top of the Introduction tab
Added an Immortal column to the Crew tab
Changed the highlighting of the Name column on the Crew tab to reflect immortalization rather than just fully fused
It is quite a bit slower than Leshy's unmodified sheet, but there is a lot of duplicate info in the new tabs and the existing ones; I didn't want to do major surgery to the original sheet so it would be easier to replicate this in the future, but it should be possible to speed things up by getting rid of duplication.
I don't contribute enough to the forums to be allowed to post a link, but prefix the following with https:// and you can see what I've attempted here: docs.google.com/spreadsheets/d/1-wjztyIa-eLcxGAtWYJoKkgL6cwFN6C1ATiZ4kp4rCk/edit?usp=sharing
Comments welcome!
Thanks again to Leshy for the existing DNA Checklist, to the DataCore folks for processing the raw DisruptorBeam data into something coherent, and to Captain Idol for giving me the idea to import the DataCore file.
Awesome work! When I first made the Datacore tool it was originally going to be my Lazy Leshy sheet intended for easy imports but spun off into something more useful. Good stuff!
I noticed that the duplicate stats on the introduction page reads from the wrong field.
Is: =countif('DataCore Massaged'!$L$3:$L,true)
should be: =countif('DataCore Massaged'!$H$3:$H,true)
I really like this sheet but have a difficult time keeping it current with my actual crew. So I decided to forgo manual data entry and use the DataCore crew.csv file. I added a tab to the DNA Checklist that can be used to import the DataCore file, at which point the Crew tab is automatically updated.
This is super cool stuff. I will have a look at exactly how you have done this and see if it's straightforward enough to implement, and how to ensure that there would be as little duplication as possible to keep the sheet as speedy as possible. I like that this does not involve having to log in through the sheet itself, and does not prevent anyone from manually inputting their crew if they so prefer.
In the meantime, I did do a quick update with the most current crew in, now in the OP!
In the meantime, I did do a quick update with the most current crew in, now in the OP!
Amazing work, as always!
Found a little bug since the renaming of "Dark Ages" McCoy: I'm guessing you cut/inserted the row in the Stats sheet, but the formulas on the Introduction worksheet are now not taking him into account for calculations. e.g. Crew immortalized says =countif(Stats!$AZ$6:$AZ,"C")+countif(Stats!$AZ$6:$AZ,"F"), but "Dark Ages" McCoy is on Stats!AZ5, so he's not counted for that number. Similar thing for the Crew Obtained formula.
Also the stats for Christine Chapel are a tiny bit off. She has DIP 313, SCI 459 (correct) and MED 685 @ 3/4 FE.
Just noticed that Lenara Khan is listed on the Stats tab, but not visible on the Crew tab. That messed up some calculations that I had added on my sheet, but adding the extra row at 452 fixed it.
I'm not sure if this is just me, but I tried your version of the DNA spreadsheet. The lazy person in me loves importing the crew, so thank you. When I did so, the sheet listed 1* Ensign Mayweather as active, which he is certainly not. Not sure if that's just me, a bug, or something I did, but wanted to let you know.
I'm not sure if this is just me, but I tried your version of the DNA spreadsheet. The lazy person in me loves importing the crew, so thank you. When I did so, the sheet listed 1* Ensign Mayweather as active, which he is certainly not. Not sure if that's just me, a bug, or something I did, but wanted to let you know.
I can confirm this. All my 1* crew are frozen but shown as active, except for Stargazer Picard and Will Riker. See my screenshot:
EDIT:
After some investigation I found that those two are the only 1* characters that I don't have multiple copies of. Therefore they are the only ones with value=1 in the 'Immortal' column of 'DataCore Import' table. Characters with multiple frozen copies have numbers greater then 1.
Solution: On the 'DataCore Massaged' table in 'Frozen' column the formula should look for values > 0 in 'DataCore Import' column 'Immortal':
i.e. 'DataCore Massaged'!M3
After some investigation I found that those two are the only 1* characters that I don't have multiple copies of. Therefore they are the only ones with value=1 in the 'Immortal' column of 'DataCore Import' table. Characters with multiple frozen copies have numbers greater then 1.
Solution: On the 'DataCore Massaged' table in 'Frozen' column the formula should look for values > 0 in 'DataCore Import' column 'Immortal':
i.e. 'DataCore Massaged'!M3
=if(isblank($B3),"",'DataCore Import'!G2>0)
Nice call -- thanks! I updated my sheet with the fix.
Spukkz explained it correctly: when you did not have Laas, you did not need him for anything. The mission that he is eligible for, was already covered by your Lt. Airiam, so the sheet told you that you could safely airlock Laas if you got him. When you eventually immortalised him and entered him into the sheet, it told you that as a crew member now on your active roster, he could complete that particular mission for you.
Eriond75 is incidentally also correct, in that if you enter a crew as 2/4, they may not meet the same requirements as they do at 4/4, so the recommendations will also differ. However, I do not think that you did that in this particular case.
To answer your other question, there are basically six possible scenarios:
Crew is active, and meets remaining requirements: Active
Crew is active, and does not meet remaining requirements: Space/Freeze
Crew is frozen, and meets remaining requirements: Unfreeze
Crew is frozen, and does not meet remaining requirements: Frozen
Crew is unobtained, and meets remaining requirements: Need
Crew is unobtained, and does not meet remaining requirements: Space
You can set the 'Mark as keep' checkbox to override the recommendations 'Space/Freeze', 'Frozen', and 'Space'.
And as per above for the episode missions: if you have an active crew member who can complete that mission, unobtained and frozen crew will no longer have that mission counted towards their requirements.
Leshy and others, I'd like to revisit this. I'm at a place where my crew is choked with 1/4s that do nothing for me. I've generally been reluctant to toss them, since the next event could provide one of them as a 3/4 reward.
It would be great if I had some indicator that the crew doesn't improve my game. I'm thinking that the easiest way to do this would be to individually add the crew, mark them 4/4, active and let the sheet do its calculations. If I get a "space/freeze", I can safely discard the 1/4.
Is that the easiest way to manage this aspect of crew?
Leshy and others, I'd like to revisit this. I'm at a place where my crew is choked with 1/4s that do nothing for me. I've generally been reluctant to toss them, since the next event could provide one of them as a 3/4 reward.
It would be great if I had some indicator that the crew doesn't improve my game. I'm thinking that the easiest way to do this would be to individually add the crew, mark them 4/4, active and let the sheet do its calculations. If I get a "space/freeze", I can safely discard the 1/4.
Is that the easiest way to manage this aspect of crew?
That sounds like a good idea but have a look at the wiki for final numbers and skill sets too as they might be useful under certain situations. For instance Sarina was listed for freezing on my list but I know she's very useful due to her rare combination of secondary and tertiary skills.
Leshy and others, I'd like to revisit this. I'm at a place where my crew is choked with 1/4s that do nothing for me. I've generally been reluctant to toss them, since the next event could provide one of them as a 3/4 reward.
It would be great if I had some indicator that the crew doesn't improve my game. I'm thinking that the easiest way to do this would be to individually add the crew, mark them 4/4, active and let the sheet do its calculations. If I get a "space/freeze", I can safely discard the 1/4.
Is that the easiest way to manage this aspect of crew?
That sounds like a good idea but have a look at the wiki for final numbers and skill sets too as they might be useful under certain situations. For instance Sarina was listed for freezing on my list but I know she's very useful due to her rare combination of secondary and tertiary skills.
I'd add that within this subset of 1/4 crew, it's generally a good idea to prioritize keeping main cast crew such as kirk, over one-off episode crew such as orion slaver. Maybe Slaver will one day eventually have one event, but even lousy kirk variants WILL have events.
After some investigation I found that those two are the only 1* characters that I don't have multiple copies of. Therefore they are the only ones with value=1 in the 'Immortal' column of 'DataCore Import' table. Characters with multiple frozen copies have numbers greater then 1.
Solution: On the 'DataCore Massaged' table in 'Frozen' column the formula should look for values > 0 in 'DataCore Import' column 'Immortal':
i.e. 'DataCore Massaged'!M3
=if(isblank($B3),"",'DataCore Import'!G2>0)
Nice call -- thanks! I updated my sheet with the fix.
Klobetime, I'm getting the following error when I try to add a crew manually:
I've got a behold and I'm trying to see if one or the other crew helps more. The error might be connected to the sheet limiting entry to only the datacore input.
I've got a behold and I'm trying to see if one or the other crew helps more. The error might be connected to the sheet limiting entry to only the datacore input.
Message me with the steps you are using to manually add a crew member and I'll be happy to take a look!
Is that the easiest way to manage this aspect of crew?
Easier for me is to clear the row altogether - Active and Keep both unchecked, stars-owned column blank. If it says Space in that state, then the sheet doesn't consider this crew an upgrade for you even if maxed.
It is a little annoying to me that it doesn't show you the "this crew will be a voyage upgrade for you at 4/4, but not at your current 3/4" type logic if you already have data in the row. But at least pretending you don't have the crew at all does let you peek.
Is that the easiest way to manage this aspect of crew?
Easier for me is to clear the row altogether - Active and Keep both unchecked, stars-owned column blank. If it says Space in that state, then the sheet doesn't consider this crew an upgrade for you even if maxed.
It is a little annoying to me that it doesn't show you the "this crew will be a voyage upgrade for you at 4/4, but not at your current 3/4" type logic if you already have data in the row. But at least pretending you don't have the crew at all does let you peek.
I don’t want to speak for Leshy, but I don’t understand the sheet to work that way. I wish it did because that would make assessing a new crew much easier.
What is required to update the crew list in the DNA spreadsheet? Is it only the creation of new lines in the Stats sheet? What information is needed for each new crew, and what are the sources (is everything in the Wiki)?
@klobetime Does your updated version require extra steps in the new sheets, or is simply adding new lines to the Stats sheet enough?
I know that we all recognize the huge amount of work @Leshy puts in to produce this for us. I figure we’ll get it when we get it. If I gain new crew in between updates, I just write them on a post-it to add when the new sheet comes in.
@klobetime Does your updated version require extra steps in the new sheets, or is simply adding new lines to the Stats sheet enough?
New lines in the Stats and Crew sheets should be enough, although the character pages on https://stt.wiki/ don't all have complete numbers for each star level. I use the formulas on https://stt.wiki/wiki/Crew_Progression to extrapolate the missing values and shade them so I can tell which ones are guesses -- I think this is what @Leshy did as well but not sure.
Hello, everyone! I come bearing some good news and some bad news.
The good news is that I have finally made some time to sit down and include the latest crew, and implement klobetime's suggestion to allow an import from the automatically generated CSV file from DataCore. I have not copied the solution entirely one-on-one, as I would personally prefer to not include another column of checkboxes on the Crew sheet (especially considering that with the way Google Sheets works, these auto-filled checkboxes can actually not be manually (un)checked anymore).
There are also a few small differences in the implementation in the background, but essentially it is still klobetime's suggestion and ideas. Major props to klobetime for it!
Version 3.50b is now up in the opening post, and from my quick checking should work with both an automated import and manual import. There's probably some bugs that may still pop up as more people use it, so I have tentatively titled it as a beta version
The bad news is that this will likely be the last update from my end (perhaps barring any serious bugs in this version that I may still be able to quickly resolve). In addition to having had a fairly busy period IRL, I have also found that I have burned out on/gotten bored of ST:T, and have not really logged in for pretty much the last month or so.
I may return at some point, but that could easily be in one month as it could be in six, twelve, or even not at all. As such, I will also no longer maintain this spreadsheet. It would of course be great if someone else wants to take up the glove – I would be entirely happy to share the dev version of the sheet (it has a few minor extra things to the release versions, although nothing hugely major) or to provide some pointers on how it works to anyone who wants to take over. Either mention me in this thread or send me a PM – I'll get an email notification and should be able to get back to you.
At the very least, I would like to thank all of you for using the sheet, the support, ideas, comments and the regular nice words. It's been fun!
It would be great if I had some indicator that the crew doesn't improve my game. I'm thinking that the easiest way to do this would be to individually add the crew, mark them 4/4, active and let the sheet do its calculations. If I get a "space/freeze", I can safely discard the 1/4.
Is that the easiest way to manage this aspect of crew?
The way I have always done it, is to not include them in the sheet (or for that matter, level them in-game). When empty, the sheet will show the recommendations as apply at FF status. I then simply mark how many stars I have for the not-fully-fused crew in the Notes column.
This way, I can easily see which crew I have at what amount of stars, while the sheet still indicates how useful that crew will be for me once fully fused. I have personally found this the easiest way to keep track.
I use the formulas on https://stt.wiki/wiki/Crew_Progression to extrapolate the missing values and shade them so I can tell which ones are guesses -- I think this is what @Leshy did as well but not sure.
The dev version of the sheet has a lookup tab where you can enter the minimum and maximum values for crew skills. The sheet then simply looks up existing crew with the same or very similar progression value, and provides you with the necessary intermediate values. They are normally correct or very close. I also posted a standalone version of that sheet once in a different thread.
@Leshy
Too bad that you feel like parting ways. But sometimes life 'comes-a-callin' and we have to heed the call of nature and move on.
Like your sheet! It's a little too advanced for me right now at my stage in the game but it is still great!
Remember: it's just a game, not a matter of life and death!
Comments
In addition to changing the Level and Active columns on the Crew tab, I made a couple of other small edits as well:
It is quite a bit slower than Leshy's unmodified sheet, but there is a lot of duplicate info in the new tabs and the existing ones; I didn't want to do major surgery to the original sheet so it would be easier to replicate this in the future, but it should be possible to speed things up by getting rid of duplication.
I don't contribute enough to the forums to be allowed to post a link, but prefix the following with https:// and you can see what I've attempted here: docs.google.com/spreadsheets/d/1-wjztyIa-eLcxGAtWYJoKkgL6cwFN6C1ATiZ4kp4rCk/edit?usp=sharing
Comments welcome!
Thanks again to Leshy for the existing DNA Checklist, to the DataCore folks for processing the raw DisruptorBeam data into something coherent, and to Captain Idol for giving me the idea to import the DataCore file.
The work around solution that I have found, is to jump to games.disruptorbeam.com where there is a LOGOUT screen. Then you will need to clear your cache and log in again, so that the game data is downloaded again, instead of just refreshing from cache. I've found the process to be a bit picky, so it may work for you to just try a different browser for the process.
Maybe you should not mark 'Active' all crew but only those à 100/100 ?
Awesome work! When I first made the Datacore tool it was originally going to be my Lazy Leshy sheet intended for easy imports but spun off into something more useful. Good stuff!
I noticed that the duplicate stats on the introduction page reads from the wrong field.
Is: =countif('DataCore Massaged'!$L$3:$L,true)
should be: =countif('DataCore Massaged'!$H$3:$H,true)
In the meantime, I did do a quick update with the most current crew in, now in the OP!
Amazing work, as always!
Found a little bug since the renaming of "Dark Ages" McCoy: I'm guessing you cut/inserted the row in the Stats sheet, but the formulas on the Introduction worksheet are now not taking him into account for calculations. e.g. Crew immortalized says =countif(Stats!$AZ$6:$AZ,"C")+countif(Stats!$AZ$6:$AZ,"F"), but "Dark Ages" McCoy is on Stats!AZ5, so he's not counted for that number. Similar thing for the Crew Obtained formula.
Also the stats for Christine Chapel are a tiny bit off. She has DIP 313, SCI 459 (correct) and MED 685 @ 3/4 FE.
https://docs.google.com/spreadsheets/d/1qrJ0gLoPDXN-ofu1YeCiRaemaR_i0Jn_M4FWitTg1Zc/edit?usp=sharing
Merry Christmas!
Klobetime:
I'm not sure if this is just me, but I tried your version of the DNA spreadsheet. The lazy person in me loves importing the crew, so thank you. When I did so, the sheet listed 1* Ensign Mayweather as active, which he is certainly not. Not sure if that's just me, a bug, or something I did, but wanted to let you know.
I can confirm this. All my 1* crew are frozen but shown as active, except for Stargazer Picard and Will Riker. See my screenshot:
EDIT:
After some investigation I found that those two are the only 1* characters that I don't have multiple copies of. Therefore they are the only ones with value=1 in the 'Immortal' column of 'DataCore Import' table. Characters with multiple frozen copies have numbers greater then 1.
Solution: On the 'DataCore Massaged' table in 'Frozen' column the formula should look for values > 0 in 'DataCore Import' column 'Immortal':
i.e. 'DataCore Massaged'!M3
Nice call -- thanks! I updated my sheet with the fix.
Leshy and others, I'd like to revisit this. I'm at a place where my crew is choked with 1/4s that do nothing for me. I've generally been reluctant to toss them, since the next event could provide one of them as a 3/4 reward.
It would be great if I had some indicator that the crew doesn't improve my game. I'm thinking that the easiest way to do this would be to individually add the crew, mark them 4/4, active and let the sheet do its calculations. If I get a "space/freeze", I can safely discard the 1/4.
Is that the easiest way to manage this aspect of crew?
That sounds like a good idea but have a look at the wiki for final numbers and skill sets too as they might be useful under certain situations. For instance Sarina was listed for freezing on my list but I know she's very useful due to her rare combination of secondary and tertiary skills.
I'd add that within this subset of 1/4 crew, it's generally a good idea to prioritize keeping main cast crew such as kirk, over one-off episode crew such as orion slaver. Maybe Slaver will one day eventually have one event, but even lousy kirk variants WILL have events.
Klobetime, I'm getting the following error when I try to add a crew manually:
=iferror(vlookup($A5,'DataCore Massaged'!$C$3:$M,3,false),0)
I've got a behold and I'm trying to see if one or the other crew helps more. The error might be connected to the sheet limiting entry to only the datacore input.
Message me with the steps you are using to manually add a crew member and I'll be happy to take a look!
Easier for me is to clear the row altogether - Active and Keep both unchecked, stars-owned column blank. If it says Space in that state, then the sheet doesn't consider this crew an upgrade for you even if maxed.
It is a little annoying to me that it doesn't show you the "this crew will be a voyage upgrade for you at 4/4, but not at your current 3/4" type logic if you already have data in the row. But at least pretending you don't have the crew at all does let you peek.
I don’t want to speak for Leshy, but I don’t understand the sheet to work that way. I wish it did because that would make assessing a new crew much easier.
Pwetty Pwetty Pwease .... with cherry on top ..
I'm just hoping for an update with the new crew.
I hope we'll get a 4.x version with auto import from datacore or it's source. But a simple crew manifest update would still be a gift.
@klobetime Does your updated version require extra steps in the new sheets, or is simply adding new lines to the Stats sheet enough?
Check out our website to find out more:
https://wiki.tenforwardloungers.com/
@USS Dart : no need for post-it; just sort by version.
New lines in the Stats and Crew sheets should be enough, although the character pages on https://stt.wiki/ don't all have complete numbers for each star level. I use the formulas on https://stt.wiki/wiki/Crew_Progression to extrapolate the missing values and shade them so I can tell which ones are guesses -- I think this is what @Leshy did as well but not sure.
I haven't digested everything the original sheet does, but I took a crack at adding the new crew to my version of the sheet: https://docs.google.com/spreadsheets/d/1su8kt4oy1rRlU7D3z5E8TOIGDXFVJXa_dFDP8Vk-Bts/edit?usp=sharing
Take a look, and let me know where I made any missteps.
The good news is that I have finally made some time to sit down and include the latest crew, and implement klobetime's suggestion to allow an import from the automatically generated CSV file from DataCore. I have not copied the solution entirely one-on-one, as I would personally prefer to not include another column of checkboxes on the Crew sheet (especially considering that with the way Google Sheets works, these auto-filled checkboxes can actually not be manually (un)checked anymore).
There are also a few small differences in the implementation in the background, but essentially it is still klobetime's suggestion and ideas. Major props to klobetime for it!
Version 3.50b is now up in the opening post, and from my quick checking should work with both an automated import and manual import. There's probably some bugs that may still pop up as more people use it, so I have tentatively titled it as a beta version
The bad news is that this will likely be the last update from my end (perhaps barring any serious bugs in this version that I may still be able to quickly resolve). In addition to having had a fairly busy period IRL, I have also found that I have burned out on/gotten bored of ST:T, and have not really logged in for pretty much the last month or so.
I may return at some point, but that could easily be in one month as it could be in six, twelve, or even not at all. As such, I will also no longer maintain this spreadsheet. It would of course be great if someone else wants to take up the glove – I would be entirely happy to share the dev version of the sheet (it has a few minor extra things to the release versions, although nothing hugely major) or to provide some pointers on how it works to anyone who wants to take over. Either mention me in this thread or send me a PM – I'll get an email notification and should be able to get back to you.
At the very least, I would like to thank all of you for using the sheet, the support, ideas, comments and the regular nice words. It's been fun!
The way I have always done it, is to not include them in the sheet (or for that matter, level them in-game). When empty, the sheet will show the recommendations as apply at FF status. I then simply mark how many stars I have for the not-fully-fused crew in the Notes column.
This way, I can easily see which crew I have at what amount of stars, while the sheet still indicates how useful that crew will be for me once fully fused. I have personally found this the easiest way to keep track.
The dev version of the sheet has a lookup tab where you can enter the minimum and maximum values for crew skills. The sheet then simply looks up existing crew with the same or very similar progression value, and provides you with the necessary intermediate values. They are normally correct or very close. I also posted a standalone version of that sheet once in a different thread.
Too bad that you feel like parting ways. But sometimes life 'comes-a-callin' and we have to heed the call of nature and move on.
Like your sheet! It's a little too advanced for me right now at my stage in the game but it is still great!
My Captain Idol's
My DataCore page
My Spreadsheet