Supply Tracking - Part 3

So, forever ago, I did a couple of posts on Supply tracking and promised a third tutorial about using an excel spreadsheet for keeping track of your supplies and how to use the spreadsheet and Supply Tracker to easily create a credits list. (You can find the first and second posts here and here.)

To follow along with this tutorial you can download a copy of the excel spreadsheet here. (A PDF of these instructions is also included in the download.) The sample spreadsheet has just a few products in it, to show the different fields and what kind of data would be put in them. Once you are finished with the tutorial, you can simply delete the sample products and use the spreadsheet for your own supply tracking!

Keeping track of purchased supplies:

Maybe you have a great memory and know every item that is in your stash, but I have quite often heard the complaint of people buying the same product twice! Keeping a spreadsheet of the products you have purchased can save you from making that mistake. (Provided you look at the spreadsheet, of course! LOL!)

In the spreadsheet provided, there are four simple fields for keeping a list of your supplies.

Column B: Initials/Prefix - Use this field for the designer initials or 'prefix' that the designer uses on their files.

Column C: Designer Name - Use this field for the full name of the designer.

Column D: Product Name - Full name of the product purchased. I find it easiest to copy and paste this from the store where I purchased the product.

Column E: Product Link - Put the full URL or 'link' of the product in this field.

These are the four basic fields that I recommend tracking for supplies - the ones that are necessary for the 'calculated' fields that I will explain below. If desired, you might like to add other columns such as commercial use, price, store name, etc.

When looking at my supply list, I find it easiest to have it sorted by Designer. Sorting an excel spreadsheet is very simple.

Put your cursor somewhere in the column you want to sort. Select the A>Z button on the toolbar and the entire spreadsheet will be sorted, based on the column selected.

To sort by a different column, simply put your cursor in a different column and use the same sort button to resort by the selected field.

To sort by more than one column at a time:
Go to Data>Sort
In the Sort Dialog Window Select the first field you want to sort by. In this sample, I chose 'Initials/Prefix'.

Choose the second field you want to sort by in the 'Then by' field. In this sample, I chose Product Name.

Make sure the 'My list has header row' option is selected.

Click OK.

Using the spreadsheet for website and gallery links:

If you keep track of your supplies in an excel spreadsheet, using the four fields recommended above, it is very simple to use the same spreadsheet to make some calculated fields for posting supply lists to websites, blogs and galleries. The sample spreadsheet provided already has the formulas entered into fields F, H & J. If you put your data in columns B,C,D and E of the spreadsheet, the following fields will be calculated for you.

Column F: Blog/Store/Website format - This column is a calculated field. It uses the data from Columns D & E (Product Name and Product Link) to create a correctly formatted line of code that can be used to insert links in blogs and websites and such.

Column H: Gallery w/Links - This column is another calculated field. It uses the data from Columns D and E to create a line of code for putting linked credits in Galleries.

Column J: Gallery - no Links - This column combines the Product name and Designer name, for listing credits in galleries that do not allow linking.

Columns G & I: These two columns were added to simple give a 'buffer' between the calculated fields. This just makes it a bit easier to distinguish one column from the other.

Using the 'Select' Column:

Here is a trick for using the spreadsheet to it's fullest. If you have created a cleaned up supply list for your layout, as explained in this post, open it along side of your Supply Tracking Spreadsheet. Or, you can work right from your memory, layout in photoshop, or whatever other 'tool' you use for keeping track of which supplies you used on a layout.

For each supply used, place an 'X' (or character of your choice) in the Select column of the corresponding product in the Supply Tracking SpreadSheet.

With your cursor in the Select field, use the A>Z sort button to sort the spreadsheet. This will sort all of the 'selected' products to the top of the spreadsheet.

You can now highlight all of the desired calculated fields.

Press Ctrl-C to copy the data to the clipboard and then use Ctrl-V to paste them to your blog, website or gallery as needed.

If you are posting as list to a blog or website, it is helpful to use the html coding < ul  > before your list of links and < / ul > after the list (remove the spaces).

When you are ready to use your Supply Tracking Spreadsheet for another layout, simply remove all the x's from the select column, resort as desired and start over!

Hope this helps speed up your supply tracking and makes giving credit a bit easier! Have a blessed day!