Another crafting spreadsheet - with a little automation!

DewiMorgan · May 2014

I am far from the first to have thought of this, and there are many others out there, but here's my pitch at it:

http://goo.gl/N5Lypd

The first tab, "Summary", is the auto-calculated summary page: you don't need to change anything on there.

Next is the "Bank" tab - for items you have stored, but are not studying yet. Items filled in here will show up on your summary page as yellow.

Next are the eight "Alt" pages - one for each of your characters.

Make a copy of the sspreadsheet ("File" menu -> "Make a copy" menuoption). Fill out at least one of those eight tabs with your character's skill levels and what you've researched, and the respective squares in your summary table will fill out in green.

There's an OnEdit() script, so that any time you change a trait from '-' to 'Researching', the research timer will fill, according to your character's skill level in that crafting area. This doesn't quite handle correctly the case where you started researching, and then your skill level changed partway through... but it does the best it can. And any time you change it from 'Researching' to 'Known', the timer will clear again.

I'm considering making a central spreadsheet, like the one by @Cheatingdeath23 except mine would just pull the summary pages from anyone who has a similar layout of spreadsheet, using Google's ImportRange() function.

Anyone is welcome to use, reuse, and remix my spreadsheet design in their own work: I release it under CC0 and/or public domain. I'm not sure the OnEdit script will copy over if you copy the spreadsheet, but if not, just ask and I'll share here or something.

But if my spreadsheet's not what you're looking for, I've found a few others here on the forums, most of which provide a whole lot that mine doesn't:

CheatingDreath23's metalist (many users, but only one character each): http://forums.elderscrollsonline.com/discussion/comment/809218

@Keron's Crafting Helper spreadsheet (far more than just a trait tracker!): http://forums.elderscrollsonline.com/discussion/comment/752471

@Lupinemw‌'s list: https://docs.google.com/spreadsheet/ccc?key=0AsC3RvXpep-qdDdIbVl2OG16MG5YdURCdkZhQ3BRNEE&usp=drive_web#gid=0

Trade helper mods: http://www.esoui.com/downloads/cat40.html

I'd also be delighted to trade traits with anyone - you can contact me ingame as @Farrier (US Server).

Kemono · May 2014

DewiMorgan wrote: »

I'd also be delighted to trade traits with anyone - you can contact me ingame as @Farrier.

You forget to mention most important thing -what server you on?

Btw i dont like summary page in that tab, cos its missing research status (only show own/missing/banked)
You have Weaponsmithing listed twice, if anything second should be Woodworking. Also i would preffer if you move Shield below rest of woodworking - i was looking for them there, but you grouped them with armors

Ok personal trait exchange
For the begining
I can provide you (if you EU located) with Infused axe, precise ligthing, sharpened frost for traits im looking for:

Heavy gauntlets: exploration
Light hat:training
sword: training

im also looking for 1h mace:training (but you have that one banked )

DewiMorgan · May 2014

Good point about weaponsmithing - fixed!

I could add "researching" as another yellow level, but it might look messy; the summary page needs to show the statuses of up to eight alts, and showing eight timers would be bad. I could put an R after each alt-number, I guess, to indicate that it's researching, and then color in yellow unless there are numbers without an R... Hrm. I could even have it show a timer, and only show the shorter form if two or more alts have researched the trait.

All that would make for some horrendous expressions in the cells, though

So for the moment, stuff that's researching is considered "known" for the summary table.

I'd also like to have the empty fields on the AltN tabs marked as green (known by another alt), yellow (not known but banked) or red (neither known nor banked). This would let me see more easily what I want to research for that character.

I grouped shields with armor because they're an armor, and it's more useful to have the blacksmithing armor and weapon traits grouped (since there are more of them) than woodworking ones. But the lines can be dragged around and will still continue to work anyway.

I regret that I am on the US server, so cannot trade with you.

That is good info to include on the spreadsheet itself, and I have added it, and another field for accountname.

DewiMorgan · May 2014

v1.0.2 is up: From the suggestion by @Kemono the summary sheet now shows items that are being researched with the letter R, and highlights them in blue.

Specifically, it highlights in this order:
Any alt knows it = Green, with the alt number(s) listed.
Any alt is researching it = blue, with the alt number(s) listed with the letter R.
Banked = yellow, with the word "Bank".
Otherwise = Red, and no text.

I looked into getting conditional formatting to work across sheets (so I could have different highlighting for unknown cells on each alt depending whether another alt knew or was researching it, whether it was banked, etc): sadly, conditional formatting's "custom expression" only lets you reference cells on the same sheet. So I may have to do this with another script instead.

DewiMorgan · May 2014

v1.0.3:
ADDED: Empty cells in tabs highlight with the summary table's status color.
ADDED: Summary page now totals rows as well as columns.
ADDED: Simple total to the individual bank and alt tabs.
REMOVED: Lookup table for the timers.
REMOVED: Lookup column hidden on each sheet.
REMOVED: Lots of unnecessary empty columns and rows.
FIXED: Changing some fields to "Researching" would clobber the wrong field.
FIXED: Some fields used the wrong skill to calculate time.
CHANGED: Minor layout cleanup.

Think this is about as thorough as it can be now, so I'll go back to playing.

If enough people express an interest, I'll work on something to compile multiple peoples' summary tables into a single comparison spreadsheet.

Lupinemw · May 2014

I'm so glad I only have 1 character

Though my 2nd toon might do Potions as its the only thing my main one isn't covering.

DewiMorgan · May 2014

The spreadsheet should be useful even to those with only one character - the timers, trackers to say which research items you have available and where they are stored, and so on.

The extra tabs for other characters are just there because I can

I only use the first two, myself.

Rather than storing research items on a mule, it takes less inventory space to get your mule to train on those items, and craft them up when you're ready to have your main learn them. It also means that you don't accidentally lose them somehow; and means you can then trade that trait with others.

It's also faster: best case, it'll take about 300 days to train all traits on a single character; but over eight characters, you could do it in a couple of days. Of course, the ultimate aim is to have one character know all traits, since then you can craft the best sets; but in the intervening months, it's nice to be able to trade and craft with those traits that your main has not yet learned.