Hi,
I tried to add the information how many skills a crew member has. I tried
=COUNTIF(Stats!A:A, "A80")
also with
"=A80"
, which does not work. On the other hand
=COUNTIF(Stats!A:A, "Agent Harris")
gives the correct result. Do you have any advise on how to do it, or even can incorporate the column in your master?
The reasoning behind this is, that I don't want to keep high max crew with only two stats, because high max is mostly used in voyages, where 3 stats are usually better.
Your first example will work if you remove the quote marks, eg:
=COUNTIF(Stats!$A:$A, $A80)
If you add in the quote marks, it will try to find the actual text "A80" in column A of the Stats tab – which obviously is not there.
I actually like your idea, however, and have expanded on it a bit as well. I am just waiting for the stats for the new crew for this weekend's event to be released later today, then I will release a new version that has this implemented by default.
@Leshy if you are interested, I have a sheet of calculations I append to your work which does a lot of counting by skill/status/Rarity (and # of crew I should freeze but still keep around for whatever reason)
I'm always open to suggestions, so feel free to share!
Incidentally, version 2.15 is now in the OP, which adds the skills of each crew to the Crew Members page, listed from main stat to secondary stat, to third stat (if applicable) by base value. For those who want to be able to filter by 3-skill crew only, there is also a hidden column showing whether each crew has 1, 2, or 3 skills.
The latest crew, including those for upcoming weekend, are also in. As usual, let me know if anything is not working as it should, or if there are any suggestions!
Suggestion: On the Crew Members worksheet, adding a "What do I have?" column that isn't an explicit trigger for the Active/Cryo'd evaluations, but useful for allowing others to consult on what you have and what might best be served by freezing.
If you add in the quote marks, it will try to find the actual text "A80" in column A of the Stats tab – which obviously is not there.
Well, now that I read it, it's quite obvious and I feel a little stupid :-(
Your extension is good, I thought about having the primary stat visible as well, but had no idea how to do that.
I found a collection bug, Nella isn't part of the daystrom collection.
The sherwood bug is also visible for me. I have Alan cryo'd but the sheet doesn't recognize it.
But collections aren't my priority. The bigger problem I have is that the new founder cannot crit Feed a Fever, but that's not your fault ;-)
Regarding the 'Sherwood Forest' collection: I see I copy-pasted some values instead of formulas. It's fixed and working properly again now in the 2.16 version that is up.
Regarding the Nella Daren thing: I see a few traits have gotten move around. They're fixed for Nella in the current version, but I will do a full check later on to see if anything else got moved around.
Started looking at the skill count feature you introduced. If 1701 Jadzia Dax is not the first character after sorting, the numbers disappear above her position. The way I found to correct it is to put the ArrayFormula on another sheet and then have the D column have a =Sheet!Cell formula placed in it. Only looked at it for a few minutes, but it seems to work. Also, it seems like some of the formulas are missing from the Character column on the Crew Member sheet. A simple copy paste fixed those.
Nevermind on that. The array still screws up on sort. But if you put the formula in D1 and modify it to read '=ArrayFormula(countif(Stats!$A:$A,'Crew Members'!$A$1:$A))' then it seems to work. Only problem is that the column header reads 1 instead.
EDIT
Apparently, the formula swears, as it's being ***Tsk Tsk***'d. Basically, start at A1
The Guardians of Tomorrow Protecting the Galaxy's Future from itself
If 1701 Jadzia Dax is not the first character after sorting, the numbers disappear above her position.
You're right. I wrote it as an ArrayFormula, but forgot that if crew are re-sorted, it moves around and does not fill upwards. It has been replaced with regular formulas now – that way it does not require fiddling with the header row.
Also in 2.18: the latest crew, and Cryostasis Collection requirements are now shown individually, so that it is easier to spot which crew are part of which collection, or what crew belong to multiple ones.
As of most recent update I'm now getting characters showing "why do I still need.." for missions I have already marked completed (namely Feed a Fever, Field Modifications, Evacuation Orders), and for collections I have fully completed. Any idea what happened?
I have Locutus active for several gauntlet and collection reasons, but his "why do I still need.." just appears green as active with no explanation.
Yes, it looks like a few references in the formulas that take care of the display of requirements got offset by a few columns, so a few odd things could – and did – show up. I've quickly corrected them in the 2.18 version that is up, and the correct requirements should now display again.
Yes, it looks like a few references in the formulas that take care of the display of requirements got offset by a few columns, so a few odd things could – and did – show up. I've quickly corrected them in the 2.18 version that is up, and the correct requirements should now display again.
Leshy, potential new bug...on the latest version, Temporal Shift Kes and Talos IV Spock are showing up as active even though they are clearly marked as cyro'd. I tried deleting and re-marking, same result. Anyone else experiencing this? Not sure why only these two, or whether both being Rare Blues which start with T has something to do with it.
The worst part is it is making recall the horrific experience of trying to FE Spock.
Thanks, as ever!
Hey, they seem to be fine on mine. Can you share a copy of your sheet? I can then have a look at what's going on yours and see if it's indeed a bug that I should/can fix
Edit: Thanks, had a look – there is apparently a line break character in the 'Active' column on both these crew in your sheet. While it does not show a visible character, Google Sheets still considers the cell to be non-empty, and thus it considers those crew marked as active. Select the cell, press 'Delete' and everything should be back to normal
Hi Leshy,
First THANKS A LOT FOR YOUR HARD WORK !
Then I tought of an improvement if you have time and are willing to.
In the crew member "Active" column you can allow to enter not just any mark but the actual number of star of the crew. IE for Yelgrun, for example, we can enter 3 in the column when the character is 3 stars out of 4 and FE. So we can mark the character owned but It wont count as fully owned in the collection calculations. As well, in the mission advices, a 1/5 character wont be able to do the mission but a 3/5 of the same character could do it.
Hey Mururoa! Your request is indeed not impossible, but it would require a lot of additional work to compile and maintain all the additional stats in the sheet, as they would need to be there for each crew member at each amount of stars. Unless there is a better way to efficiently pull the stats from an external source automatically, it's likely not something that will be added soon, unfortunately.
Ok, I may have a look at formulas to enable that for me just for collections.
So I could mark character owned before all stars acquired.
Personally, I go into the Stats tab and modify the ones I have fully equipped - mind you, I only do it for golds. Would be way too much work for the purples too lol
Hi Leshy,
First THANKS A LOT FOR YOUR HARD WORK !
Then I tought of an improvement if you have time and are willing to.
In the crew member "Active" column you can allow to enter not just any mark but the actual number of star of the crew. IE for Yelgrun, for example, we can enter 3 in the column when the character is 3 stars out of 4 and FE. So we can mark the character owned but It wont count as fully owned in the collection calculations. As well, in the mission advices, a 1/5 character wont be able to do the mission but a 3/5 of the same character could do it.
My workaround is create another column (Column G)and use that for incomplete crew I own.
It will not update the Keep/Need/Space/Frozen flag, but it helps me balance what I own and what I'm building.
It also does not accurately update the "Number Not Airlocked" Calculation. But if you change that calculation from
=counta('Crew Members'!$E2:$F)
to
=counta('Crew Members'!$E2:$G) it works.
I actually use the notes column already to mark what value of max rarity I have for characters I don't have FF so it works already with the fantastic job Leshy has done. With the knowledge that it throws off the calc by marking *every* active crew in the active column and not just my FF I know who is best to work on to level up to 99 FE to complete a mission when I get those extra stars. I have enough crew now that I have 5 missions left and can juggle voyages/shuttles, though the best way to Max them out for rewards still confuses me 😋
---- The Guardians of Tomorrow Protecting the Galaxy's Future from itself
Hi, @Leshy , and thank you very much for maintaining & updating this wonderful resource. I was checking my copy of v2.20 against the in-game Collections and found a discrepancy:
Herbert Rossoff has the "Veteran" trait and counts towards the "Valor in Combat" collection, but his line entries on the (hidden) Stats page do not have X's in the Veteran column (FS, rows 732-733), so the Collections sheet count is off by 1.
LLAP
"In the short run, the game defines the players. But in the long run, it's us players who define the game." — Nicky Case, The Evolution of Trust
Comments
I tried to add the information how many skills a crew member has. I tried also with , which does not work. On the other hand gives the correct result. Do you have any advise on how to do it, or even can incorporate the column in your master?
The reasoning behind this is, that I don't want to keep high max crew with only two stats, because high max is mostly used in voyages, where 3 stats are usually better.
Your first example will work if you remove the quote marks, eg:
If you add in the quote marks, it will try to find the actual text "A80" in column A of the Stats tab – which obviously is not there.
I actually like your idea, however, and have expanded on it a bit as well. I am just waiting for the stats for the new crew for this weekend's event to be released later today, then I will release a new version that has this implemented by default.
Incidentally, version 2.15 is now in the OP, which adds the skills of each crew to the Crew Members page, listed from main stat to secondary stat, to third stat (if applicable) by base value. For those who want to be able to filter by 3-skill crew only, there is also a hidden column showing whether each crew has 1, 2, or 3 skills.
The latest crew, including those for upcoming weekend, are also in. As usual, let me know if anything is not working as it should, or if there are any suggestions!
PS. While I have Will and Tuck marked for Cryo and Robin and Alan for active.
Well, now that I read it, it's quite obvious and I feel a little stupid :-(
Your extension is good, I thought about having the primary stat visible as well, but had no idea how to do that.
I found a collection bug, Nella isn't part of the daystrom collection.
The sherwood bug is also visible for me. I have Alan cryo'd but the sheet doesn't recognize it.
But collections aren't my priority. The bigger problem I have is that the new founder cannot crit Feed a Fever, but that's not your fault ;-)
Regarding the Nella Daren thing: I see a few traits have gotten move around. They're fixed for Nella in the current version, but I will do a full check later on to see if anything else got moved around.
Thanks for reporting!
Started looking at the skill count feature you introduced. If 1701 Jadzia Dax is not the first character after sorting, the numbers disappear above her position. The way I found to correct it is to put the ArrayFormula on another sheet and then have the D column have a =Sheet!Cell formula placed in it. Only looked at it for a few minutes, but it seems to work. Also, it seems like some of the formulas are missing from the Character column on the Crew Member sheet. A simple copy paste fixed those.
Nevermind on that. The array still screws up on sort. But if you put the formula in D1 and modify it to read '=ArrayFormula(countif(Stats!$A:$A,'Crew Members'!$A$1:$A))' then it seems to work. Only problem is that the column header reads 1 instead.
EDIT
Apparently, the formula swears, as it's being ***Tsk Tsk***'d. Basically, start at A1
Protecting the Galaxy's Future from itself
Also in 2.18: the latest crew, and Cryostasis Collection requirements are now shown individually, so that it is easier to spot which crew are part of which collection, or what crew belong to multiple ones.
I have Locutus active for several gauntlet and collection reasons, but his "why do I still need.." just appears green as active with no explanation.
Yes, it looks like a few references in the formulas that take care of the display of requirements got offset by a few columns, so a few odd things could – and did – show up. I've quickly corrected them in the 2.18 version that is up, and the correct requirements should now display again.
The worst part is it is making recall the horrific experience of trying to FE Spock.
Thanks, as ever!
Edit: Thanks, had a look – there is apparently a line break character in the 'Active' column on both these crew in your sheet. While it does not show a visible character, Google Sheets still considers the cell to be non-empty, and thus it considers those crew marked as active. Select the cell, press 'Delete' and everything should be back to normal
First THANKS A LOT FOR YOUR HARD WORK !
Then I tought of an improvement if you have time and are willing to.
In the crew member "Active" column you can allow to enter not just any mark but the actual number of star of the crew. IE for Yelgrun, for example, we can enter 3 in the column when the character is 3 stars out of 4 and FE. So we can mark the character owned but It wont count as fully owned in the collection calculations. As well, in the mission advices, a 1/5 character wont be able to do the mission but a 3/5 of the same character could do it.
So I could mark character owned before all stars acquired.
Personally, I go into the Stats tab and modify the ones I have fully equipped - mind you, I only do it for golds. Would be way too much work for the purples too lol
My workaround is create another column (Column G)and use that for incomplete crew I own.
It will not update the Keep/Need/Space/Frozen flag, but it helps me balance what I own and what I'm building.
It also does not accurately update the "Number Not Airlocked" Calculation. But if you change that calculation from
=counta('Crew Members'!$E2:$F)
to
=counta('Crew Members'!$E2:$G) it works.
The Guardians of Tomorrow
Protecting the Galaxy's Future from itself
Herbert Rossoff has the "Veteran" trait and counts towards the "Valor in Combat" collection, but his line entries on the (hidden) Stats page do not have X's in the Veteran column (FS, rows 732-733), so the Collections sheet count is off by 1.
LLAP