Gear Planner

Share strategies, experience and tips & tricks.
Akari
Posts: 38
Joined: Fri Jul 29, 2016 7:58 pm

Gear Planner

Postby Akari » Sun Jan 01, 2017 3:34 am

With the major holiday patch seeming to now highly encourage a lot of non-questing players to explore efficient/basic skilled gearing, I wanted to try sharing a sort of digestible version of my personal team building spreadsheet for others to feel free to utilize and hopefully find helpful.

http://bit.ly/SHGearPlanner

Image


From my city to yours, a (belated) Merry Christmas and a Happy New Year! :D

---
Note: The tool will only work if you can edit the document. I have put this document on read-only mode to preserve the formulae for others to re-access and copy anytime. To use the gear planner, first make sure you are signed in to your Google account then go to File > Make a Copy of this Google Spreadsheet and save it on your own Google Drive. Open and edit your own copy to begin using the gear planner.

Your team set-up is 1-page print friendly! On your own copy, just go to File > Print or Download as PDF. If you want to share your set-up with others, you can convert your PDF file to JPG or PNG using any of the online converters (pdftopng.com) or you can also take a screenshot.

Remember that you can sort any of the columns in ascending or descending order to help you view and assess your teams better, despite the sheet protection warnings. Best practice always would be to duplicate the Gear Planner sheet first and then work on the copy so you can always leave the original sheet fresh and untainted in case anything gets muddled. ;)

UPDATES:
17/1/1 - Team skill summary should be done soon. You can follow it's progress in the Temporary Sheet/Tab within the same document.
17/1/2 - File updated and now includes team skill summary and Fastidious/Supplier/Treasure Hunter/Scavenger/Detect Secrets data.
17/1/9 - Did the initial layout and formatting for the full version, viewable on the test sheet/temporary tab, to include power calculations.
17/1/14 - Posted sample line-up to test the Gear Planner Lite tab's 1-page print friendliness.
17/1/17 - Added two sample line-ups within the file as hidden sheets for user references. Go to View > Hidden Sheets to access.
17/1/21 - Added the two new skilled blueprints from the Chinese New Year Packages (Lucky Rooster & Celebration).
17/2/1 - Updated posts and screenshots and tweaked the sample sheets.
17/2/6 - Added a beginner friendly sample team line-up (hidden sheet code: F41L) and updated screenies/posts accordingly.
17/2/14 - Added the two new skilled blueprints from the Valetine's Day Packages (Love & Care). Added Guns as a second weapon type for Lorelei.
17/3/5 - Fixed Fiora's weapon slot error on the main sheet as well as in all sample sheets and posted screenshots on this thread.
17/3/14 - Added Heroic Pack #2 contents (New T6 Hero Prof. Charles, Mystic Ring, and Mystic Vest) and updated Magic Find on the Skill Summary. Fixed Mystic Vest chest type error.
17/4/8 - Added the two new skilled blueprints from the Easter Time Packages (Eggciting & Eggcellent) and updated BP Checklist to include all new skilled pack items introduced in 2017.
17/4/10 - Corrected formulae error where the Skill Summary tally was lacking for 1 hero ever since the Heroic Pack #2 changes were introduced.
Last edited by Akari on Sun Apr 09, 2017 9:39 pm, edited 13 times in total.
Akari x roister
NoGoodNamesLeft
bit.ly/SHGearPlanner

Akari
Posts: 38
Joined: Fri Jul 29, 2016 7:58 pm

Re: Gear Planner

Postby Akari » Sat Jan 14, 2017 3:14 am

Sample ready-to-use F2P-friendly 4 6-man team line-ups with 6-7 Support/Resourceful each.

Sheet code: F41L
Image


Sheet code: F45HL
Image
Last edited by Akari on Sat Mar 04, 2017 9:33 pm, edited 4 times in total.
Akari x roister
NoGoodNamesLeft
bit.ly/SHGearPlanner

wigglez
Posts: 7
Joined: Mon Nov 23, 2015 8:16 pm

Re: Gear Planner

Postby wigglez » Sun Jan 15, 2017 3:29 am

Thank you for posting and providing this! I'm unable to get the other team builder spreadsheet working here on this forum, so this is a great option for me.

Akari
Posts: 38
Joined: Fri Jul 29, 2016 7:58 pm

Re: Gear Planner

Postby Akari » Mon Jan 16, 2017 7:39 pm

wigglez wrote:Thank you for posting and providing this! I'm unable to get the other team builder spreadsheet working here on this forum, so this is a great option for me.


You are welcome! Hope the format proves to be useful and not confusing for you. Please don't hesitate to let me know if you have any feedback for improvement purposes. Thank you!

PS. I have also updated the file just now to include a few hidden sheets featuring sample line-ups for extra references. You can re-save an updated copy if you'd like access to them as well. Cheers!
Akari x roister
NoGoodNamesLeft
bit.ly/SHGearPlanner

internetz
"Great" Mastery Member
Posts: 184
Joined: Mon May 16, 2016 1:22 pm

Re: Gear Planner

Postby internetz » Fri Feb 03, 2017 3:32 am

i like it, but ... i've got some improvements to recommend.

first , the data validation should take into account the info from the bp checklist page. (items you have) i'm currently working on building out queries that do this in my copy, provided the user marks the bps they have with a lowercase 'x' in column A

example query to output a list of the bps that a user has marked (sdat is defined as "'BP Checklist'!A3:I260")

Code: Select all

=query(sdat,"select B ,C, D, E, F, G, H, I where A = 'x' ")

then to break that list into the individual lookups, I'm running this for each unique type (48 total unique queries for this part needed, hdat is defined as 'improve formulas'!A31:H647, and is in a new sheet where the previous query dumps it's output)

Code: Select all

=ArrayFormula("("&QUERY(Hdat,"Select A where C = 'Axes' or C = 'Spears' or C = 'Hammers/Maces' ")&") "&QUERY(Hdat,"Select B where C = 'Axes' or C = 'Spears' or C = 'Hammers/Maces' "))

this is headed with "[Axes/Spears/Hammers]" and has a range named "axespeham", running from the header to 135 rows down (for future proofing)

then for each hero that was using the sorted list of axes,spears,and hammers, you instead set data validation from a range and set it to axespeham. The only negative with this approach is a slight bit of overhead, and the loss of the subsections in the validation popup.

I wanted to add a way to see what ability each thing added, but that broke the hardcoded formulas in the rest of the sheet, and it would require a lot more time than I currently have to fix that. If the () wasn't needed around the level, it would be mush more efficient, with only one query needed per unique type of validation.


edit: link to modified version that only shows validation for things you have:
https://docs.google.com/spreadsheets/d/ ... sp=sharing

Akari
Posts: 38
Joined: Fri Jul 29, 2016 7:58 pm

Re: Gear Planner

Postby Akari » Mon Feb 06, 2017 6:54 am

internetz wrote:i like it, but ... i've got some improvements to recommend.

first , the data validation should take into account the info from the bp checklist page. (items you have) i'm currently working on building out queries that do this in my copy, provided the user marks the bps they have with a lowercase 'x' in column A

example query to output a list of the bps that a user has marked (sdat is defined as "'BP Checklist'!A3:I260")

Code: Select all

=query(sdat,"select B ,C, D, E, F, G, H, I where A = 'x' ")

then to break that list into the individual lookups, I'm running this for each unique type (48 total unique queries for this part needed, hdat is defined as 'improve formulas'!A31:H647, and is in a new sheet where the previous query dumps it's output)

Code: Select all

=ArrayFormula("("&QUERY(Hdat,"Select A where C = 'Axes' or C = 'Spears' or C = 'Hammers/Maces' ")&") "&QUERY(Hdat,"Select B where C = 'Axes' or C = 'Spears' or C = 'Hammers/Maces' "))

this is headed with "[Axes/Spears/Hammers]" and has a range named "axespeham", running from the header to 135 rows down (for future proofing)

then for each hero that was using the sorted list of axes,spears,and hammers, you instead set data validation from a range and set it to axespeham. The only negative with this approach is a slight bit of overhead, and the loss of the subsections in the validation popup.

I wanted to add a way to see what ability each thing added, but that broke the hardcoded formulas in the rest of the sheet, and it would require a lot more time than I currently have to fix that. If the () wasn't needed around the level, it would be mush more efficient, with only one query needed per unique type of validation.


First off, I am glad you like it, and I'm more than happy to hear any suggestions for improvement. Secondly, a HUGE thanks for these, and the patience to tweak it to reality! I cannot stress it enough, for in truth, I wanted to be able to do exactly that with that BP Checklist sheet, but my Google Sheet skillz are next to zilch (this was tbh just a learn-on-the-fly fun project for me to 'code', just incidentally/might as well be SH related lol.) The codes you provided here, before now, have been completely alien to me...and I'm super stoked to know they're actually available! You can see the limitations in the way I hardcoded almost everything and have to manually update per slot every time a new BP comes along... so yeah. The subsections can go really, poor effort to put some sort of organization to the lengthy lists. I'm beginning to understand how the codes you've put in works, and I see what you mean in that I'll likely need to do a bit of an overhaul to address the separation of item name and level for coding efficiency, while still being available and presentable in the main sheet. Just lemme down a few dozen more cups of coffee before I fully digest these and integrate them to the master file, and if you don't mind, I'll consult with you again before launching v2.0. :D
Akari x roister
NoGoodNamesLeft
bit.ly/SHGearPlanner

NoInspiration
"Good" Mastery Member
Posts: 96
Joined: Mon Jan 25, 2016 7:17 am

Re: Gear Planner

Postby NoInspiration » Tue Feb 07, 2017 7:16 am

Small question.

Is is possible to sort the heroes by group without screwing the spreadsheet?

Will it still work if I copy paste hero lines? If I add a sort to first column?

Akari
Posts: 38
Joined: Fri Jul 29, 2016 7:58 pm

Re: Gear Planner

Postby Akari » Tue Feb 07, 2017 4:08 pm

NoInspiration wrote:Small question.

Is is possible to sort the heroes by group without screwing the spreadsheet?

Will it still work if I copy paste hero lines? If I add a sort to first column?


Yup! Sorting any of the columns, despite the edit warning, is fine (and recommended!) :D

Copy pasting anything onto the cells might mess with the codes and formulas though. If you copy the same item name onto a different hero equipment slot, the drop down options will be transferred too, even if they might not be applicable to that hero anymore.

At best, you can override the content of the equipment cells by typing in directly what you want them to wear, without destroying the hidden codes per cell. Autofill is also harmless I think.

In any case you can make a duplicate copy of the Gear Planner sheet first and work on the copy so that uou can always pull up the pristine version if sith happens. :D
Akari x roister
NoGoodNamesLeft
bit.ly/SHGearPlanner

internetz
"Great" Mastery Member
Posts: 184
Joined: Mon May 16, 2016 1:22 pm

Re: Gear Planner

Postby internetz » Wed Feb 08, 2017 7:38 pm

Akari wrote:
First off, I am glad you like it, and I'm more than happy to hear any suggestions for improvement. Secondly, a HUGE thanks for these, and the patience to tweak it to reality! I cannot stress it enough, for in truth, I wanted to be able to do exactly that with that BP Checklist sheet, but my Google Sheet skillz are next to zilch (this was tbh just a learn-on-the-fly fun project for me to 'code', just incidentally/might as well be SH related lol.) The codes you provided here, before now, have been completely alien to me...and I'm super stoked to know they're actually available! You can see the limitations in the way I hardcoded almost everything and have to manually update per slot every time a new BP comes along... so yeah. The subsections can go really, poor effort to put some sort of organization to the lengthy lists. I'm beginning to understand how the codes you've put in works, and I see what you mean in that I'll likely need to do a bit of an overhaul to address the separation of item name and level for coding efficiency, while still being available and presentable in the main sheet. Just lemme down a few dozen more cups of coffee before I fully digest these and integrate them to the master file, and if you don't mind, I'll consult with you again before launching v2.0. :D



No problem. I may not see messages you leave me here right away, but if you ask for edit privs to that sheet when you want to contact me, I'll check the forums to see what's up :)

Damador
Posts: 9
Joined: Sat Feb 11, 2017 12:05 am

Re: Gear Planner

Postby Damador » Sat Feb 11, 2017 5:28 am

any tip where I can put switch Lorei :) i need starting setup for premium player but with lower powssible items ... any hints welcomed


Return to “Strategy & Guides”



Who is online

Users browsing this forum: No registered users and 3 guests

©2015 Cloudcade, Inc. All Rights Reserved.