The power of speadsheets
- This topic is empty.
-
My day job requires me to make heavy use of Excel and database files. My gear list can get out of hand since I have a lot of time to think about it and less time to use it.
I have all my gear listed in columns of:
Basic Category – Base Weight or Consumable
System – Packing, Shelter, Cooking/Hydration …..
Item Name – "name of item"
OZ – oz per unit
Quantity – how many (more useful on consumables)
Total OZ – Quantity x OZThen I have a column called "Select" where I place a 1 or 0. If I need it for a trip it gets a 1.
On a new tab I have a pivot table which neatly arranges everything by Basic Category/System/Name total OZ per item and grand total per system. The pivot is filtered to only show stuff I ticked with a 1 on the gear list.
It may sound complex but my Excel skills are crazy. It takes me minutes to build something like this (sans typing in the values).
Nice thing is that I can go down the list and mark 1, refresh the pivot table and have a ready to go list.
Here is how on eof the pivots look:
Instead of 'select' I do something similar and call it 'quantity' so I can carry 0, 1 or as many as I like. This work well for stuff that might carry a bunch of (ie. water bottles, batteries etc). It doesn't hide the stuff I'm not taking though, so I do have another page with 'other gear' listed that I manual add and remove but my list doesn't fluctuate much because I usually sell gear I don't use regularly. I can see how if you had a huge gear closet and you carry a highly different gear list everytime then making the gear disappear that you aren't taking would be very valuable.
Here's how I have it setup:
Here is what I use. I build it a couple years ago, and haven't gotten around to sophisticating it. It really is a rough draft. Eventually I will add a grams converter and would like to play around with nutritional values. If anyone wants a copy, PM me. However, I probably can't send it until next week, as tomorrow I have to get ready for a trip and will be gone until Sunday. It will only work on MS Excel for Windows (2000 and higher), because it is dependent upon VBA code.
What I wanted to do is use a spreadsheet that would be easy to do "What if." As I enter items, the main category weights are calculated at the top. Also all my gear is listed on one sheet, and I can filter it so it only shows the gear I have chosen for a particular trip. It hides my entire gear list, but when the Unfilter button is clicked, all items on the list are visible.
There are drop down lists for items. The picture below is a scree shot of what it looks like after I have filtered it, by just clicking the filter button (hides all gear items not in the current kit). It can also sort the list, un-filter it show all my gear, etc. Here are some descriptions of items in the maroon row.
Status: Choose "In" if I am taking it.
Category: Choices are Base, Consumable or Wear.
Class: Cannot change. Used for sorting. I should probably hide it it.
Class Name: Can add anything you want and it will sub-sort it. Currently using: Pack, Sleeping, Clothes, Kitchen, Utility, Kitchen and Office.
Description: Self evident.
QTY: How many you have.
Lbs: Enter Lbs. Not needed as you can instead enter ounces (i.e. 32 ounces and leave the lbs empty).
The last 3 columns are used for calculations and should probably be hidden.
Buttons:
– Sort: Sort by Category, then Class, then description of item (easier to locate an item).
– Filter: Hides all items that are not chosen for a particular trip.
– Unfilter: Shows all items in your gear closet.
– Print Filtered: Prints you kit for your trip.
– Print Unfiltered: Prints everything in your gear closet.
The gear list below if the final Kit for a trip. Notice that the categories are at the top for quick and easy review. Shows Big 3, Base, Consumables, Worn, and FSO.
- You must be logged in to reply to this topic.
