Looks like we are missing a Chakotay unless I'm blind - Resolute Chakotay
I was also wondering if it would be worth saving this crew in version order? I'm not sure how others import the data each update but I have to sort by version and add everyone in. I may be the only person who manages to semi-regularly make a complete mess of importing the crew but thought I'd ask!
As this is first post on this topic - I really want to send my thanks for maintaining this list, it makes it so much easier to manage everything!
Looks like we are missing a Chakotay unless I'm blind - Resolute Chakotay
This is correct, I haven't seen him aswel. I know that it is possible to keep him on the version number list. I think @Leshy will do this in the next update.
I was also wondering if it would be worth saving this crew in version order? I'm not sure how others import the data each update but I have to sort by version and add everyone in. I may be the only person who manages to semi-regularly make a complete mess of importing the crew but thought I'd ask!
I sort both the old and the new sheet 1st alpabetically, then by version. Also make sure no filters are set, been there got the t-shirt. Then copy column B and after that E-G.
Seemed to be the fastest way to get everything over nicely.
I was also wondering if it would be worth saving this crew in version order? I'm not sure how others import the data each update but I have to sort by version and add everyone in. I may be the only person who manages to semi-regularly make a complete mess of importing the crew but thought I'd ask!
I sort both the old and the new sheet 1st alpabetically, then by version. Also make sure no filters are set, been there got the t-shirt. Then copy column B and after that E-G.
Seemed to be the fastest way to get everything over nicely.
I'd suggest that you first sort by version, THEN alphabetically when copying crew from one version to another. The advantage to doing it this way, is after pasting your columns you can scroll to the bottom and see the most recent crew all grouped together to easily select those you have obtained/frozen before re-sorting them to your preferred order.
I'd suggest that you first sort by version, THEN alphabetically when copying crew from one version to another. The advantage to doing it this way, is after pasting your columns you can scroll to the bottom and see the most recent crew all grouped together to easily select those you have obtained/frozen before re-sorting them to your preferred order.
Well sorting 1st alphabetically then by version does that - the latest crew is at the bottom.
Sorting by version then alphabetically just has the table sorted alphabetically...
I'd suggest that you first sort by version, THEN alphabetically when copying crew from one version to another. The advantage to doing it this way, is after pasting your columns you can scroll to the bottom and see the most recent crew all grouped together to easily select those you have obtained/frozen before re-sorting them to your preferred order.
Well sorting 1st alphabetically then by version does that - the latest crew is at the bottom.
Sorting by version then alphabetically just has the table sorted alphabetically...
It sounds like you might be using one sort method at a time separately? In google sheets, choose Data, Sort Range..., "add another sort column". Now you're doing 2 sorts at once, but not independent of each other. First sorting by version will group all the same version cards grouped together, and then your second row of sorting being alphabetical is a sub-sort of within that versioned crew, they are sorted alphabetically.
This is not "just has the table sorted alphabetically", because it's ALSO sorted by version first, just alphabetical within that version.
The alphabetical part may not always be necessary, but I have found this method has always meant accurate copy/paste columns for me since I started using this tool long ago.
I'd suggest that you first sort by version, THEN alphabetically when copying crew from one version to another. The advantage to doing it this way, is after pasting your columns you can scroll to the bottom and see the most recent crew all grouped together to easily select those you have obtained/frozen before re-sorting them to your preferred order.
Well sorting 1st alphabetically then by version does that - the latest crew is at the bottom.
Sorting by version then alphabetically just has the table sorted alphabetically...
It sounds like you might be using one sort method at a time separately? In google sheets, choose Data, Sort Range..., "add another sort column". Now you're doing 2 sorts at once, but not independent of each other. First sorting by version will group all the same version cards grouped together, and then your second row of sorting being alphabetical is a sub-sort of within that versioned crew, they are sorted alphabetically.
This is not "just has the table sorted alphabetically", because it's ALSO sorted by version first, just alphabetical within that version.
The alphabetical part may not always be necessary, but I have found this method has always meant accurate copy/paste columns for me since I started using this tool long ago.
Yes doing two separate sorts, didn't bother with the extended sorting yet - gives me an error anyways because the combined cells on top get in the way. Doesn't matter, the result is the same
click the #4 number beside the header row (stars, name, etc) to highlight that row, then hit CTRL-SHIFT-(downarrow)
this'll just highlight the data rows and the one header row you want. then on the screen I guided you to, ensure you select the radial button for "first row is a header row" or however it's worded. now you can multi-sort despite the "crew members" heading.
I even go as far as 3 layers of sorting after updating versions. Active (Z-A order), Stars (Z-A order), Name (A-Z order). now my legendaries are on top, active super-rares next, frozen/inactive crew after, each section sorted alphabetically.
If you just highlight the first 3 rows and delete them, you can use the sort normally. @Leshy, this is a lot of extra inconvenience for a title that isn't necessary. We have the tab name. Once again, I recommend removing the title in the spreadsheet.
Hi, I started using this sheet recently (3.13), works wonders, helped me fix some hard nodes. Today I tried to update with the most recent version (3.14) by sorting on version, and then copying the bottom rows into my own sheet. I know it takes time to calculate so I've waited a bit. However: I get this:
As you can see, some really strange things are happening, though I followed instructions as given in the sheet. Variants don't match, some crew have wrong nrs of stars, etc.
What am I doing wrong? Any tips for me?
Thnx!!
Hi, I started using this sheet recently (3.13), works wonders, helped me fix some hard nodes. Today I tried to update with the most recent version (3.14) by sorting on version, and then copying the bottom rows into my own sheet. I know it takes time to calculate so I've waited a bit. However: I get this:
As you can see, some really strange things are happening, though I followed instructions as given in the sheet. Variants don't match, some crew have wrong nrs of stars, etc.
What am I doing wrong? Any tips for me?
Thnx!!
It works the other way around, you copy the data from your old sheet to the new one (the new one has the data for the new crew)
Again the short and easy version for non-sheet-geeks:
- copy a sheet from the new sheet to your docs space and name it as you like (just keep a version number)
- make sure the old sheet has no filters set
- sort the old sheet 1st by column A (Name), then by column H (Version)
- sort the new sheet 1st by column A (Name), then by column H (Version)
- mark B5 down to the bottom of your old sheet
- copy into B5 of your new sheet
- mark E5 to G5 and down to the bottom of the old sheet
- copy into E5 of your new sheet
Would it be possible to have column for Big Book Tier numbering, I usually add them myself but it's a bit of a chore when I move to a new sheet.
Thanks as always for the amazing resource.
Would it be possible to have column for Big Book Tier numbering, I usually add them myself but it's a bit of a chore when I move to a new sheet.
Thanks as always for the amazing resource.
I was about to start using the notes column for this but then I found two BBoB sites I use actually had a difference. One had two crew at tier 12 but another had one at 12 and the other at 13. It got me thinking how often the tier may change and unless the sheet pulls in data from a single site it may not work. Plus I'm not sure I'd want to add extra load to the sheet as it is now with a data import refresh
---- The Guardians of Tomorrow Protecting the Galaxy's Future from itself
I had been using the Doro site and the notes column, it helped visually work out who to prioritize based on usefulness. Either to freeze for collection or get better voyagers.
There are three BBoB sites. The Datacore site should be almost verbatim of the Google Doc site. The Doro site appears not to be updated as frequently.
I've added a spreadsheet to my DNA checklist that has all of the data from the BBoB. I add a column to the crew tab that then does a lookup to that sheet. That way, I'm not messing with Leshy's data on the Stats sheet. But, to each their own.
The Guardians of Tomorrow Protecting the Galaxy's Future from itself
I am certain this has been addressed, so apologies for likely rehashing old news, but...
How do I fix the dummyfunction thing? Specifically, the formula in !StatsBH was broken by google. Every single cell references row 5 (and keeps that value in an auto fill), and there is a “dummyfunction” qualifier at the start of the equation.
I am certain there is a quick and easy fix. I just can’t find it.
I am certain this has been addressed, so apologies for likely rehashing old news, but...
How do I fix the dummyfunction thing? Specifically, the formula in !StatsBH was broken by google. Every single cell references row 5 (and keeps that value in an auto fill), and there is a “dummyfunction” qualifier at the start of the equation.
I am certain there is a quick and easy fix. I just can’t find it.
Thanks in advance.
As a follow up...
Turns out that the sheet is a google doc, not an excel doc. I saved it as an excel doc, which broke a number of fields. I re-saved it as a google doc and it worked.
Thanks for the assistance, everyone who offered...
I am always open to suggestions. If you have a great idea, or a working example, I am always interested. I cannot guarantee that every idea will make it in, but I will definitely have a look.
If you just highlight the first 3 rows and delete them, you can use the sort normally. @Leshy, this is a lot of extra inconvenience for a title that isn't necessary. We have the tab name. Once again, I recommend removing the title in the spreadsheet.
The main reason the header is in, is so that the rows of the crew on the Crew sheet match up to those on the Stats sheet. It's rather helpful while editing the sheet, so I am inclined to leave that one as is.
Unfortunate bug with the Event settings, when choosing "Yar" in the Variants of, it also selects Yarnek as Event crew.
It is a bit of an old bug, due to the way the search formula works in Google Sheets. I have now replaced it with a regex search method that should end this pesky bug once and for all.
Would it be possible to have column for Big Book Tier numbering, I usually add them myself but it's a bit of a chore when I move to a new sheet.
I have actually thought about this before. I agree that it would indeed be hugely helpful, however:
It is a bit of work to extract the tier numbers from the Big Book and to format them properly for inclusion in the DNA Checklist. I would be interested in hearing from you and @Ward Serpentine how you are including them; eg. do you have a very quick method for doing so? It would require updating the figures for each update of the sheet, unless it would have to pull them from an external source (but I'd rather have it not rely on external sources).
The sheet will not always be up to date with the BBoB, which may be updated more frequently or have been updated more recently than the sheet.
I am not sure whether @Automaton_2000 would be happy with including the tiers numbers in this sheet. I'd have to check with him whether he would be okay with it - I obviously do not want to draw people away from his amazing resource.
Incidentally, there is a new version available with the latest crew in and a few small additions and fixes!
@Leshy I simply created another sheet (didn't want to mess with the Stat sheet), then put another column on the Crew sheet with a lookup function. About every two weeks or so, I review the BBoB (https://datacore.netlify.com/bb/) and update my tier data.
It's a manual process that I do for myself. I have added a lot of stuff to my DNA sheet to include my ship levels, informational links, skirmish farming tab, etc. that gives me most of the information I need in one place. I still reference the BBoB when reviewing my crew to see the justification as to why a character is ranked the way they are.
The Guardians of Tomorrow Protecting the Galaxy's Future from itself
It's using the tiers and blurbs from the Big Book / Little Book with their respective author's permissions. As far as updates, usually have new crew in a few hours after they get added, and import the tiers 2-3 times a week. I don't know how long I'll keep this alive (until I get bored perhaps ), but in the meantime feel free to use it.
Leshy and Temporal7, I really can't thank you guys enough for this massive level of voluntary work you do. It gives me a big grin when I check this thread and find an updated version. A big Picard artificial heart to you and every other volunteer helping the community so much.
Thanks a lot for all the work put in here @Leshy
Just a small note: immortalized and frozen fusable crew (tuvok/neelix etc.) gets a unfreeze recommendation, but you can't fuse them when they're immortal.
Thanks a lot for all the work put in here @Leshy
Just a small note: immortalized and frozen fusable crew (tuvok/neelix etc.) gets a unfreeze recommendation, but you can't fuse them when they're immortal.
I realised that the recommendation is a bit strange on immortalised crew, but I think the sheet should indicate even in those cases that you still 'need' (a copy of) that crew if you do not have the fused crew yet.
The specific 'Unfreeze' recommendation is a bit odd in that case, I do agree. It would be a bit of work to change that and do it in a way that makes sense as well, however. Unless someone has a good idea or if I have a moment of inspiration, I will probably leave this little oddity in for now.
I would recommend against adding the Big Book tiers for two reasons;
They are a vacuum ranking and subjective, both of which are sort of the antithesis of your tool, as I understand it. Players should be utilizing the DNA to evaluate their own rosters' strengths and weaknesses.
And frankly, the Big Book is plenty available as is. It's hosted on a couple different sites and the behold bot incorporates it. There's value in having another separate tool to help players look at things from a different point of view, one tailored just to their roster. I could find tons of players to come tell you that the BB tiers don't quite align with their roster-building philosophies.
That being said, you're welcome to include them if you think that's the right thing for your tool. Thank you for asking and long live the DNA
I would recommend against adding the Big Book tiers for two reasons;
They are a vacuum ranking and subjective, both of which are sort of the antithesis of your tool, as I understand it. Players should be utilizing the DNA to evaluate their own rosters' strengths and weaknesses.
I would agree with this, I love having the big book for what it is and love having the DNA but both carry a 'use your own judgement' condition, the DNA is just more focused with regard to what crew you need for missions and collections. Both are very useful for someone aiming for gauntlet or voyage crew but you do need to take into account what other crew you have. The DNA goes part way to a balancing act so it's helpful but there are some crew that no matter who you have, you take and this is where the BBoB comes in. Also given the rate of crew inclusion, the BBoB will change crew rating and I wouldn't want to see this extra load on the sheet right now.
Both of you get a big Vulcan salute for all the effort you put in to the tools, they make the game a lot easier to play
---- The Guardians of Tomorrow Protecting the Galaxy's Future from itself
Thanks a ton to the developers of this sheet. I am still playing around with it and making it work for me, but a few quick ideas to be tossed into the kitty...
1) on the settings page, have a spot to enter the number of crew quarters avail. This could help in the recommendations for active/freeze/airlock/etc.
2) have a FE and/or level fields as input by the user. This too, could be used for freeze/airlock recommendations.
3) this would be tough, but maybe add base/prof/comb pairs? That would add a total of 30 additional columns, so it may get a bit unwieldy, but I think it could be advantageous. Maybe have these calls based on fuse-level since that data already exists on the data tab (and could be completely ignored for proficiency). Not sure if it would be advantageous to weigh the bases calc or not. I am still getting used to how that works, exactly (“and” supposedly is not a thing anymore, but higher v lower still is a thing, I think)...
Big asks, I know. And if they are not added, the sheet is still super useful. I am working on making small changes myself. Maybe I will be able to tackle one of these projects myself (???).
Comments
I was also wondering if it would be worth saving this crew in version order? I'm not sure how others import the data each update but I have to sort by version and add everyone in. I may be the only person who manages to semi-regularly make a complete mess of importing the crew but thought I'd ask!
As this is first post on this topic - I really want to send my thanks for maintaining this list, it makes it so much easier to manage everything!
This is correct, I haven't seen him aswel. I know that it is possible to keep him on the version number list. I think @Leshy will do this in the next update.
I sort both the old and the new sheet 1st alpabetically, then by version. Also make sure no filters are set, been there got the t-shirt. Then copy column B and after that E-G.
Seemed to be the fastest way to get everything over nicely.
I'd suggest that you first sort by version, THEN alphabetically when copying crew from one version to another. The advantage to doing it this way, is after pasting your columns you can scroll to the bottom and see the most recent crew all grouped together to easily select those you have obtained/frozen before re-sorting them to your preferred order.
I am also wondering about Jodmos. Maybe he is also missing?
Btw: Thanks for this awesome work
Well sorting 1st alphabetically then by version does that - the latest crew is at the bottom.
Sorting by version then alphabetically just has the table sorted alphabetically...
It sounds like you might be using one sort method at a time separately? In google sheets, choose Data, Sort Range..., "add another sort column". Now you're doing 2 sorts at once, but not independent of each other. First sorting by version will group all the same version cards grouped together, and then your second row of sorting being alphabetical is a sub-sort of within that versioned crew, they are sorted alphabetically.
This is not "just has the table sorted alphabetically", because it's ALSO sorted by version first, just alphabetical within that version.
The alphabetical part may not always be necessary, but I have found this method has always meant accurate copy/paste columns for me since I started using this tool long ago.
Yes doing two separate sorts, didn't bother with the extended sorting yet - gives me an error anyways because the combined cells on top get in the way. Doesn't matter, the result is the same
this'll just highlight the data rows and the one header row you want. then on the screen I guided you to, ensure you select the radial button for "first row is a header row" or however it's worded. now you can multi-sort despite the "crew members" heading.
I even go as far as 3 layers of sorting after updating versions. Active (Z-A order), Stars (Z-A order), Name (A-Z order). now my legendaries are on top, active super-rares next, frozen/inactive crew after, each section sorted alphabetically.
As you can see, some really strange things are happening, though I followed instructions as given in the sheet. Variants don't match, some crew have wrong nrs of stars, etc.
What am I doing wrong? Any tips for me?
Thnx!!
It works the other way around, you copy the data from your old sheet to the new one (the new one has the data for the new crew)
Again the short and easy version for non-sheet-geeks:
- copy a sheet from the new sheet to your docs space and name it as you like (just keep a version number)
- make sure the old sheet has no filters set
- sort the old sheet 1st by column A (Name), then by column H (Version)
- sort the new sheet 1st by column A (Name), then by column H (Version)
- mark B5 down to the bottom of your old sheet
- copy into B5 of your new sheet
- mark E5 to G5 and down to the bottom of the old sheet
- copy into E5 of your new sheet
Thanks as always for the amazing resource.
I was about to start using the notes column for this but then I found two BBoB sites I use actually had a difference. One had two crew at tier 12 but another had one at 12 and the other at 13. It got me thinking how often the tier may change and unless the sheet pulls in data from a single site it may not work. Plus I'm not sure I'd want to add extra load to the sheet as it is now with a data import refresh
The Guardians of Tomorrow
Protecting the Galaxy's Future from itself
I've added a spreadsheet to my DNA checklist that has all of the data from the BBoB. I add a column to the crew tab that then does a lookup to that sheet. That way, I'm not messing with Leshy's data on the Stats sheet. But, to each their own.
Protecting the Galaxy's Future from itself
How do I fix the dummyfunction thing? Specifically, the formula in !StatsBH was broken by google. Every single cell references row 5 (and keeps that value in an auto fill), and there is a “dummyfunction” qualifier at the start of the equation.
I am certain there is a quick and easy fix. I just can’t find it.
Thanks in advance.
=countif($DN5,"A")+countif($DN5,"N")+counta(iferror(filter($DO$4:$HZ$4,($DO$2:$HZ$2="PA")*($DO5:$HZ5="A")+($DO$2:$HZ$2="PF")*($DO5:$HZ5="U")+($DO$2:$HZ$2="I")*($DO5:$HZ5="N"))))+counta(iferror(filter($IA$4:$JJ$4,($IA$2:$JJ$2="P")*($IA5:$JJ5="A")+($IA$2:$JJ$2="P")*($IA5:$JJ5="U")+($IA$2:$JJ$2="I")*($IA5:$JJ5="N")+($IA$2:$JJ$2="I")*($IA5:$JJ5="A"))))+counta(iferror(filter($JK$4:$JY$4,($JK$2:$JY$2<>"C")*($JK5:$JY5<>""))))+counta(iferror(filter($JZ5:$KX5,($JZ$2:$KX$2="I")*($JZ5:$KX5="N"))))
You could copy the formula above and paste it in that cell, then fill the cells down to complete the rest of them.
Protecting the Galaxy's Future from itself
As a follow up...
Turns out that the sheet is a google doc, not an excel doc. I saved it as an excel doc, which broke a number of fields. I re-saved it as a google doc and it worked.
Thanks for the assistance, everyone who offered...
Incidentally, there is a new version available with the latest crew in and a few small additions and fixes!
It's a manual process that I do for myself. I have added a lot of stuff to my DNA sheet to include my ship levels, informational links, skirmish farming tab, etc. that gives me most of the information I need in one place. I still reference the BBoB when reviewing my crew to see the justification as to why a character is ranked the way they are.
Protecting the Galaxy's Future from itself
It's using the tiers and blurbs from the Big Book / Little Book with their respective author's permissions. As far as updates, usually have new crew in a few hours after they get added, and import the tiers 2-3 times a week. I don't know how long I'll keep this alive (until I get bored perhaps
Just a small note: immortalized and frozen fusable crew (tuvok/neelix etc.) gets a unfreeze recommendation, but you can't fuse them when they're immortal.
The specific 'Unfreeze' recommendation is a bit odd in that case, I do agree. It would be a bit of work to change that and do it in a way that makes sense as well, however. Unless someone has a good idea or if I have a moment of inspiration, I will probably leave this little oddity in for now.
They are a vacuum ranking and subjective, both of which are sort of the antithesis of your tool, as I understand it. Players should be utilizing the DNA to evaluate their own rosters' strengths and weaknesses.
And frankly, the Big Book is plenty available as is. It's hosted on a couple different sites and the behold bot incorporates it. There's value in having another separate tool to help players look at things from a different point of view, one tailored just to their roster. I could find tons of players to come tell you that the BB tiers don't quite align with their roster-building philosophies.
That being said, you're welcome to include them if you think that's the right thing for your tool. Thank you for asking and long live the DNA
I would agree with this, I love having the big book for what it is and love having the DNA but both carry a 'use your own judgement' condition, the DNA is just more focused with regard to what crew you need for missions and collections. Both are very useful for someone aiming for gauntlet or voyage crew but you do need to take into account what other crew you have. The DNA goes part way to a balancing act so it's helpful but there are some crew that no matter who you have, you take and this is where the BBoB comes in. Also given the rate of crew inclusion, the BBoB will change crew rating and I wouldn't want to see this extra load on the sheet right now.
Both of you get a big Vulcan salute for all the effort you put in to the tools, they make the game a lot easier to play
The Guardians of Tomorrow
Protecting the Galaxy's Future from itself
1) on the settings page, have a spot to enter the number of crew quarters avail. This could help in the recommendations for active/freeze/airlock/etc.
2) have a FE and/or level fields as input by the user. This too, could be used for freeze/airlock recommendations.
3) this would be tough, but maybe add base/prof/comb pairs? That would add a total of 30 additional columns, so it may get a bit unwieldy, but I think it could be advantageous. Maybe have these calls based on fuse-level since that data already exists on the data tab (and could be completely ignored for proficiency). Not sure if it would be advantageous to weigh the bases calc or not. I am still getting used to how that works, exactly (“and” supposedly is not a thing anymore, but higher v lower still is a thing, I think)...
Big asks, I know. And if they are not added, the sheet is still super useful. I am working on making small changes myself. Maybe I will be able to tackle one of these projects myself (???).