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.