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...
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.
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!
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.
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.
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.
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
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.
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."
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.
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).
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?
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 ) 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.
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:
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
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:
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.
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)
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
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*".
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.)
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!
Comments
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...
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.
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!
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.
Oh, wow! Good luck...
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:
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 InsiderCrew with trait changes no longer able to pass certain missions:
Crew with announced trait changes that appear to not be in game:
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.
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...
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
Aren't they both needed for Advanced Challenges this way? Fem Sci and Alien Sci.
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."
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.
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).
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?
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.
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
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.
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.
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:
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
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:
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
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.
=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*".
Check out our website to find out more:
http://tenforwardloungers.freecluster.eu
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!