Crafting Spreadsheet

[mintyfresh0604]

Sorry for the wall of text, there is a lot to cover with what I'm working on.

Summary
I started working on a spreadsheet that will track the number of materials needed to complete the crafting logs. Currently I have completed carpenter from lvl 1 to 50. This is just a first pass of all the info so there might be some numbers slightly off, but I believe it to be pretty accurate. In the spreadsheet I linked to at the bottom I have multiple pages. The first page is a list of all base mats needed in alphabetic order. Each page after that is for a different craft. Before I move on I'd like to get people's opinions as far as the layout, functionality, as well as some testing (I'll explain how it works further down).

How the spreadsheet works:
Currently everything is protected besides for column C on the All Base Mats sheet. This is where you will enter the number of materials you have.

If you have reached the required number of materials the Remaining Qty will be reduced to 0 and the cell will turn green.

As you enter items on the first page you'll see values changing in the Carpenter page as well. The way the craft pages are broken down are by the material needed for a recipe in the first column. If there are sub materials those will be found in the Submat # columns with the required amount, the amount you have, and the amount remaining. Like the first page the Remaining Qty will be reduced as you get more items and when complete will become green.

If the material needed is not a base material there are some calculations performed to determine how many you currently can build. Example: Bronze Ingot requires 2 Copper Ore and 1 Tin Ore. For every combination of this the "Have" column for Bronze Ingot will be increased by 1. If you only have 1 Copper Ore and 3 Tin Ore the Bronze Ingot "Have" qty should remain 0 until you get more Copper Ore.

Note 1: Items for job quests are taken into consideration for the counts if they are 1) materials crafted (logs, rivets, rings), or 2) quests that require 2 or more non HQ items. HQ items add to much of a variable to calculate needed materials.

Note 2: The totals assume that you have kept the crafted items needed for a later recipe. A section will be added at a later time that lists each item that is needed. This means if you did not keep the time the number of each material needed will be increased by the amount on the recipe.

In summary, the only interaction done by the user currently is on the "All Base Mats" sheet. Everything else should be getting filled in automatically (top to bottom) on each of the later pages.

Future Funtionality:
1) Items that are made by other professions will be color coordinated. The exact colors have not been decided.

2) The main page will have the ability to set the current log ranks you have completed (labeled as 1-5, 6-10, etc). This will auto fill the base materials needed to have completed this. Note however, that the current count assumes you have kept previously crafted items that are used in later recipes.

3) The level and locations of each base material will be added at some point.

Questions For the Cohhmunity:
1) Do you want the pages to be independent of each other or recognize how many of each mat previous professions have used?
Example of independent: You have 100 Copper Ore and Carpenter requires 60, and Blacksmith requires 60. Both pages will show they have 60 Copper Ore availabe
Example of non-independent: You have 100 Copper Ore and Carpenter requires 60, and Blacksmith requires 60. Because Carpenter is the first page it will show that you have 60. The blacksmith page will show that there are only 40 available (100 - 60 = 40).

1a) Of course there is also the potential of having a second spreadsheet that does the opposite. I would try to combine it with a setting, but I feel that will add too much complexity to a spreadsheet and having 2 different ones would be the easiest to work with and maintain.

2) Any feedback on layout would be appreciated.

3) If you would like to help work on this please let me know. I can provide you with the password to unprotect the spreadsheet so you can work on it. Just let me know what sheet you plan on doing so there is no overlap.

Link to access spreadsheet:
https://docs.google.com/file/d/0B9f5Pt- ... sp=sharing

[HK_51]

If you want summary of mats needed for each profession you can look here
https://docs.google.com/spreadsheet/ccc ... ZM0E#gid=8

[mintyfresh0604]

Thanks HK. I've noticed that some of my numbers are off. I'll have to go through it again to see why they vary. At least now I have something to compare to instead of just relying on myself staring at a page for hours at a time.

[HK_51]

after my post I went looking for crafting tool and look what I found :p
http://craftingasaservice.com/

[mintyfresh0604]

Yea, someone had pointed that out to me in stream the other day. I felt it was a good tool, but there was more that could be done with it, which is what I'm aiming for. I was trying to get something that would let you track progress, have more than 10 levels displayed at a time, and have other options available which I will hopefully be expanding to. This is also where I had been getting my counts and because it's only 10 levels at a time it might be why I had some higher quantities than the spreadsheet you had linked.

I would like to move my spreadsheet to a web app, but I'd need to get a server set up to run it all first which if I can figure out setting up apache2 I'd get a server from amazon's ec2. By doing this it will also let me track things more efficiently, and add better functionality to it in the long run.