Spreadsheet Update - Trade Tab

Here we are going to take a look at all the extra cards outside of the collection.  I call it the Trade tab because technically everything in this category is available for trade in order to complete the collection. This tab will look just like the Sealed tab by listing all the sets, Core, Expansion, and any opened special set that I have opened.

So you can see, we have all the sets listed on the left.  Then at the top I have split out the rarity with total column, and columns for Mythic Rare, Rare, Uncommon, Common, Land, Token, and Book.  We will get to the Book column in a second.

All of the rarity columns totals are being pulled from the set tabs in the Trade total sections.

Set Tab Total

We are looking at this section
_
  |
  |
  |
  |
  |
  |
  |
_|

















If you remember on each Set tab we have formulas that will look at the Trade columns and SUMIF up the totals by rarity.  This was done so we could see what type of rarity we had, and how much of that rarity we could put up for trade.

So on the Trade tab, we are taking all that information from all the Set tabs and compiling it to see the greater picture.  The sets are again split up by color to define the blocks, and Bold defines a Modern set, but we have added the same coloring for the rarity.  You will also see a dark grey only in the Mythic Rare column, and this was done to show that those are the sets without Mythic Rare cards.

Lets just take a look at a single set's formulas, and we will use Journey Into Nyx because we have values populated for that set.

Journey Into Nyx:
Column B: ='Journey Into Nyx'!$A$16
Column C: ='Journey Into Nyx'!$A$17
Column D: ='Journey Into Nyx'!$A$18
Column E: ='Journey Into Nyx'!$A$19
Column F: ='Journey Into Nyx'!$A$20
Column G: ='Journey Into Nyx'!$A$21
Column H: ='Journey Into Nyx'!$A$22
Column J: ='Journey Into Nyx'!$AE$1

You can see for the set Journey Into Nyx we are taking the totals from the Set tab ('Journey Into Nyx'), and we are looking as specific cells.  What are the $ for?  Well since we have a template for all our Set tabs, and you can find the same data in most of the same cells, we will use the $ when you define the cell to make sure that it does not increment when you copy a row to add a new set on the Trade tab.

What does that mean?  Well there are 4 new sets every year, and with those set adds every year I wanted an easy way to just copy a Row and paste it under the currently new set, and change the name of the set so that row would point to the new set tab name but keep the same cell values.

So, for the new set of Khans of Tarkir what I will do is create a 'Set tab' from the 'Set tab template' we created.  I would rename the Set tab to be 'Khans of Tarkir'.  Then I would go to the Trade tab, copy the Journey Into Nyx row, and insert the copied cells under the Journey Into Nyx row.  I would then rename the set name cell to be Khans of Tarkir, and change the color of the cell to light yellow to follow the Block separation.  Finally highlight the columns B, C, D, E, F, G, H, and J and click 'ctrl H' to pull up the replace sub-menu.  Find 'Journey Into Nyx' and replace it with 'Khans of Tarkir' and hit Replace All button.  This will replace the Set tab formulas with the set you are looking to change, but you need to remember to create the Set tab before trying to replace the set tab names in the formulas, or the replace will fail because the targets don't exist.

Khans of Tarkir:
Column B: ='Khans of Tarkir'!$A$16
Column C: ='Khans of Tarkir'!$A$17
Column D: ='Khans of Tarkir'!$A$18
Column E: ='Khans of Tarkir'!$A$19
Column F: ='Khans of Tarkir'!$A$20
Column G: ='Khans of Tarkir'!$A$21
Column H: ='Khans of Tarkir'!$A$22
Column J: ='Khans of Tarkir'!$AE$1

Just to give you an example of what I used to have to do before I started using the $ in the cell definition, I would do the exact same thing as above, but I would then have to go back into the cells and change the cell number because without the $, those number would change.  So if you copied that same row and copied it under the latest set, all the numbers would increment by 1 because of the way the spreadsheets work; 16 would become 17, and 17 would become 18, and so on.  Also in the same vein, if I were to paste the cells to the right by one cell B would become C, C would become D, and so on...

Now lets touch on the Column J used for 'Book'.  I have this formula looking at Cell $A$AE which is the total of all the cards that are in my tradebook for each set.
  - Earlier I explained that I wanted to keep track of all the cards in my tradebook so I could know what the value is on cards that I carry around with me for trade.  While all the totals on this sheet define the complete trading fodder in all the cards, the Book values is used just to define what I have in my trade binder.

Then at the bottom row I have the totals for each column which will give me a quick peek as to what the value is for each type of rarity.  I only find this important when I have thoughts of selling my entire collection.  Since stores really only look at valued cards, it is pretty easy to grab the Mythic Rare and Rare and set a specific % to how much I could get from a store, then lower that % for each Uncommons, Commons, and Lands.