Spreadsheet Update - Verification

3. Verification of cards in inventory vs cards in my trade inventory

So I hit an issue with having cards in my collection and then cards in my trading fodder, but my collection was not to the playset count of 4.  This feature enhancement is to validate that the collection count in Column G, if set lower than 4, Column O is empty.  Column O is the sum of Column S and T, which represent cards that are extra in the collection and cards that are in my trading book.

If the collection column has a value of less than 4 and there are no extra cards in the trading fodder it should result in "Good".  But if there is a value in the collection that is less than 4 and there is cards in the trading fodder, then the output would be set to "Fix".  But on the same hand if the collection column is set to 4 and there are cards in the trading fodder column it will also be set to "Good" because the playset would have been filled.

Column G: Has a value
- This is the total number of cards that are left in my collection.  The goal is to have a playset of 4 cards.

Column O: =S2+T2
- This column is used to add the amount of cards in the extra cards, and the amount of cards in the tradebook.

Column S: Has a value
- This column is used to keep track of the extra cards in my collection.

Column T: Has a value
- This column is used to keep track of the number of cards in my tradebook.

Column U: =IF(AND(G2<4,O2>0),"Fix","Good")
- This column is used to verify that Column G is less than 4 and Column O is greater than zero, and if that is true then Column U would be set to "Fix". If those values are not set than Column U is set to "Good".

From the example, you can see that there is a single instance of "Fix" where the collection total is set to 3 and the trading fodder is set to 2.  This means that there are cards in my trading fodder while my collection is still not set to 4.  Once I move a card from my trading fodder to my collection then it will be set to Good.

Spreadsheet Update - Tradebook List

2. Cards that I have in my tradebook

So here is the new spreadsheet again.  Now we are going to be looking at Columns S, T, AC, AD, and AE. I needed to keep track of the cards that were for trade, but also what I was keeping in my tradebook. This also helped when I was using the columns to populate what I put on deckbox.org, but the more I used deckbox the more I wanted to put all the rares I had along with uncommons that were worth more than $1.00.  I still use this for what I keep separate in my trade supply.

Ok, column S listed as "Ex" stands for Extra.  This is the count of the cards that are extra in the collection so therefore considered trading fodder.  But with the problem of bringing every extra card with you to a LGS in hopes to get a trade is a bit much.  That is why we are using column T listed as "Bk" which stands for Book or Tradebook.  This way I can keep track of my trading fodder that is worth being in a tradebook.  If something were to happen and the tradebook was stolen then I would not have to count everything over again to figure out what my inventory is.  I would only have to count what is in my tradebook fodder and not have to count everything over again.

If you now look at AC, AD, and AE.  This will give us the count, name of the card, and price total that is in the tradebook.

Column AC: =IF(T2>0,T2," ")
- This will look to see if there is a count in Column T which is the tradebook count and then populate the Column AC with the same count.

Column AD: =IF(T2>0,F2," ")
- This will look to see if there is a count in Column T which is the tradebook count and then populate the Column AD with the name of the card.

Column AE: =IF(AC2=" "," ",AC2*C2)
- This will look to see if there is a count in Column AC.  If Column AC is blank then Column AE will be blank, or if the Column AC is not blank then the count should be multiplied by the price and totaled.

Cell AE: =SUM(AE2:AE602)
This will get the total of all the prices of all the cards in the tradebook and total them up.  This will show the total price of all the cards in the tradebook for that specific edition.  This will be important on the Total tab to see a complete price total for all the cards in the tradebook.

The only reason I have done this was to split off the more valued trading cards from the extra cards of the collection.  I am using the same formulas as I did in the Card Needed columns where if there is a count I will populate the cells with the information, and if there is not a count I will leave the cell blank.

Spreadsheet Update - Cards Needed

So being away from writing I was not just sitting around doing nothing.  I was still trading and purchasing cards, but in doing so I hit some snags and needed to find out more information in concerns with my collection.

Issues that I hit:
1. Cards that I need for my Evernote listing
2. Cards that I have in my tradebook
3. Verification of cards in inventory vs cards in my trade inventory
4. Number of cards needed by color
5. Number of cards needed by rarity

1. Cards that I need for my Evernote listing
So I am not going to go into detail with how I use Evernote just yet, but I will explain the changes I made to my spreadsheet in order to accomplish what I needed.  So for this I needed to easily copy and paste the cards that I need into something mobile that I carry with me for in person trades.
___________________________

Ok so here is the new inventory sheet I have created.  Instead of doing a copy and paste of all the cards and then parsing the data in a notepad, I needed an easy way to populate some cells, do a copy and paste of those rows, sort them, and add them to a list of cards that I needed on Evernote.  So I added some Columns.

___________________________

Here is the new spreadsheet.  I am only showing the top rows, but it will be enough for you to understand the new features.  For this section we are going to concentrate on Columns W, X, Y, Z, and AA.

___________________________

Here you can see what is being populated.  You will also notice that some of the Rows are empty.  I did this so that once there was a full playset that collected then the data in those cells would be nulled out.

Column W: =IF(G2<4,D2," ")

- This will look at the G2 cell (Card Count in the collection) and see if it is below 4, and if it is then it will populate the cell with what is in cell D2.  If G2 is not less than 4 it will leave the cell blank.

- Column D is filled with the card type (ie: Artifact).

Column X: =IF(G2<4,E2," ")

- This will look at the G2 cell (Card Count in the collection) and see if it is below 4, and if it is then it will populate the cell with what is in cell E2.  If G2 is not less than 4 it will leave the cell blank.

- Column E is filled with the card rarity (ie: R for Rare).

Column Y: =IF(G2<4,F2," ")

- This will look at the G2 cell (Card Count in the collection) and see if it is below 4, and if it is then it will populate the cell with what is in cell F2.  If G2 is not less than 4 it will leave the cell blank.

- Column F is filled with the card name (ie: Altar Golem).

Column Z: =IF((4-G2>0),4-G2," ")

- This will look at the G2 cell (Card Count in the collection) and see if it is below 4, and if it is then it will populate the cell with what is in cell G2.  If G2 is not less than 4 it will leave the cell blank.

- Column G is filled with the card needed count (ie: 3, the number of the specific card for the collection to fulfil the playset needed).

Column AA: =IF(Z2=" "," ",IF(C2="SOON",0,C2*Z2))

- This will look at the Z2 cell and see if the cell is blank, if it is blank it will leave it blank.  If it is not blank then it will do another check, it will check to see if C2 contains "SOON", if it does then it will use a zero, but if it is not "SOON" it will multiply C2 with Z2.

- What this formula does is check to see if there is something in the count column for the needed cards.  If there is something there it will check to see if the price pulled from the TCGPlayer is "SOON", and if it is no that means there is a price set.  It will then multiply the card needed count in Z2 by the price in C2.

You should be able to highlight rows W, X, Y, Z, and AA and drag it down to the bottom of the sheet row populated with cards.  This should configure each of the cells with the formulas so you can see what cards are needed for your playset.

Cell Z1: =SUM(Z2:Z602)

This cell will give a total count of all the cards needed.

Cell AA1: =SUM(AA2:AA602)

This cell will give the total dollar amount needed to fill out the playset.

Online Trading - Deckbox.org

Found it, love it!

www.deckbox.org

Seriously the greatest thing I have found for trading.  I know you can sell on eBay, and you can trade to stores, or carry all your trading fodder around and trade at the LGS, but if you are looking for the throw away rares, and looking to make trades that you would normally not find at a LGS, this site is for you.

The way it works is you post your tradelist, and then create your wishlist.  You start out with zero trades and you just start trading.  You can search for specific cards, and you can also just do a search for things that are on your wishlist.  The site does a wonderful job with matching up things on your wishlist to things on others tradelists, and then posts the matches on other traders profile pages.  This means you don't need to sift through all the pages of cards on other traders pages, and you can just hit the profile pages to see if there is a good trade matchup.

Now, if you are a new trader, you will most likely ship first.  Usually you will need to get to over 10 trades before people feel better about trading with you.  Now I have only really had a couple of issues with traders on the site, but nothing pointing to straight up evil people.  Most of the issues I have had were just not very organized people.  Most of the time you can tell who those people are by looking at their tradelist.  Usually when people have un-updated lists, or don't use the condition posting accurately, or don't update the editions the cards are in, then they are more than likely a bad trader.

Granted there are some people that have been on the site for quite some time, and they were early adopters where some of the options were not available, but the site has made it really easy to add and remove cards from your tradelist.  Hopefully I can get to more blogging and share my different ways I have been able to do this quite quickly.

The Forums for BTR (Bad Trade Reports) is very useful as well, and I highly suggest sticking with waiting 2 weeks before opening something up.  I have had issues with shipments not getting to me, I have had issues with my packages getting to other people, and I have had issue with tracking not showing up.

Either way, check out the site.  I have a lot more to share about this site, but just wanted to put this out there as I am trying to do more blogging, and will update some other posts.