2005 Backpacking Light Trip Planning Spreadsheet Contest Entries

Happy trails

• The checklist is the sole input area.
• The checklist is complete, and, if prudent, broken down by discretionary/optional components (for example, tent fly, tent pegs)
• The checklist is broken down between what’s in the pack, what’s worn, what’s shared, etc.
• The checklist has a section to record reoccurring packing arrangements (for example, canoe trips, winter trips, etc.).
• A packing list is automatically generated from the gear selected. the packing list is ordered by packing order/section.
• The weight is broken down by categories. there’s a table and chart comparing these weights to other “norms” (lightweight, ultralight, etc.). (this helps locate areas for possible weight reduction.)
• A temperature analysis is also automatically generated from the gear selected. i’ve determined for myself what are the insulative values of my clothing and sleeping bags. (this helps me bring enough clothes!)
• My previous spreadsheet had a section to record the amount of use a particular piece of gear got on a particular trip. i’ve since gotten rid of this, but i think it may be useful (and enlightening) for some people to track this information.

kind regards

AutoFilter – menu is Data/Filter/Autofilter – These are the little drop down arrows in row 7, the title column. This allows the user to restrain a multi-colum list based the contents of one column. It’s a cheap and cheerful way of making the worksheet behave as a database.

You can customize the filter (select Custom) but a limitation of the autofilter function is that you can only use two values at a time.

Subtotal – The subtotal formula is used in Column 5, Rows C and D to calculate weight. Subtotal works in conjunction with the Autofilter feature. It calculates only those rows that are visible. These two cells calculate the total weight of the rows below in pounds and ounces.

Select “Bpl” from the drop down list in cell B7 of “BackpackList01”, for instance, and it calculates my winter weight list.

Validation – Validation (Data/Validation/Allow:List is applied to all the cells in colum B. This constrains the contents of the cells in that column to the items in the list B1 – B4. It prevents the user from incorrect entries in collumn C (leading space, space at end, etc.) It supplies a drop down list of correct choices whenever you’re in a cell in the column. You can, of course, have a much longer list of allowable entries.

Outlining – I use outlining to simplify navigation and to make small corrections to weight calculations. Some headings have total calculations in column F. After I weigh the individual items that go into a stuff sack, for instance I weight the total and, if there’s a difference between the total weight and the weight calculated in column F for that heading I enter a correction in Row D for that stuff sack.

Got that? Outlining is not essential for this worksheet because I don’t subtotal on outlines.

Freeze Panes – menu Window/FreezePanes – keeps the upper part of the worksheet visible at all times.

Other notes – Some items are in the list twice on purpose. I might, for instance wear a pair of socks and take an extra pair so one is part of personal clothing and other is basic pack weight.

Column E, like column B, is used to label items. But unlike column B it is not constrained to certain values. You can use any label you want. I use it to compare the weight of alternative kits – like raingear that uses a poncho compared to using a rain jacket, for instance.

Users will have to know how to insert and delete rows in excel and use outlining. Don’t insert rows at the top or at the end of the list because they might end up outside the calculalation range of the subtotal function.

Pretty geeky, but I use it every trip to generate a checklist. I mull over the gear I’m going to take and then use the autofilter function and print it up. I’m 56. If I don’t use it I forget stuff. I also print and fill out the worksheet entitled Meals for meal planning for the same reason.

Sheet 1
This one uses some Excel-specific features. There is a drop down menu which allows you to select one of four options for viewing your totals, Metric, Imperial (decimals), Imperial (pounds and ounces) and Imperial (Ounces). There is also a “list managed” sheet which allows things like filtering, sorting and hiding unchecked items. This file will mostly likely work ONLY in Excel.

Sheet 2
This is a gram-based version (i.e. enter weights in grams) that doesn’t use anything Excel-specific… so hopefully it will work in other applications… although I have not tested it in anything other than Excel. This one shows all the same info as the “fancy” sheet… but it shows it all at once. Item totals are in ounces only.

Sheet 3
Same as above, but ounce-based.

I’m attaching both the loose files and a ZIP archive of the files in case the loose files don’t come through properly. There is more I would have liked to do, but any further improvements would have involved using macros which would have a) taken a lot of time for me to learn and b) might make users uncomfortable… i.e. afraid of malicious code.

May the best list win :)

Cheers

When the file is loaded into Excel you will proably get a warning about macros, depending on the security level you have set in Excel.

Ironically, the spreadsheet doesn’t actually contain any macros, but it does contain radio buttons, which Excel apparently considers to be using code. If your security level is set at medium, you can go ahead and let it enable the “macros”. Even if the security level is set to high, which disables macros, the radio buttons still seem to work. If they don’t work, change the security level to medium. As far as I can tell, this is a classic Microsoft bug embedded in Excel.

Both files include a page with instructions on using the spreadsheet. They provide a method to allow weight entry in either grams or ounces, selectable by the user.

Even though this approaches the level of an “application” that you have in mind, it is still pretty easy to pick up and start getting useful results; but of course I’d appreciate any feedback on how to make it as user friendly as possible.

I’m looking forward to seeing the comments and suggestions from the forum. Please let me know if anything else is needed.

Enjoy!

As sent, the spreadsheet contains 3 tabs; pack list, gear inventory and BPL data. The 3rd tab is not necessary. I only used it to populate the gear inventory tab with data from the BPL web site.

This spreadsheet is designed to inventory your gear AND create pack lists. It is trip specific and allows you to select gear from your inventory.

In the Gear Inventory tab, select your gear by insert a trip name next to the item. If you are not taking the item, leave it blank.

Once all your gear is selected, go to the Pack List tab and generate the new pack list. Right click inside the data and choose Refresh data. Check Cell B1 and make sure your Trip is selected. If not click in the drop down arrow, select your trip and click OK.

Try this out and have some fun with it. Writing instructions is not my forte, so contact me if you have any questions.

Regards

It’s an updated version of an Excel model that I’ve used for several years.

To make it easier for others to try, I’ve cleaned it up a bit and added some instructions and usage notes on the first worksheet tab.

The model uses a simple flat file Excel database with each gear item recorded as a separate record (row) in the list. As you’ll note, there’s also backup data for some of the entries (those shown with blue hyperlinks).

From this database list, a variety of reports can then be generated using built-in Excel’s Pivot Table functions. Aside from offering a lot of flexibility, this reporting approach also eliminates the need to manipulate live data when creating different views of the gear list.

A subset of my own gear collection is included in the model to serve as sample data. The file also includes two sample Pivot Table reports-one in English units (ounces and pounds), and one in metric units.

In addition, I’ve done a bit more testing with the new version of Open Office (V2.0) and have added a couple of comments on the “Instructions” page about that testing.

Cheers and thanks for the contest.

I am looking forward to seeing new ideas.

Details / Introduction have been included in the first tab of the spreadsheet

Thanks!

Good afternoon. I hope I made it under the 12/1/05 wire for contest entries.

Attached are two Excel documents for your consideration.

(See attached file: my gear list template 120105.xls)(See attached file: my three season gear list 120105.xls)

The first, labeled “my gear list template 120105” is a blank spreadsheet ready for personal gear entry. Users simply enter each item of their own gear in the appropriate category and its weight in ounces. The spreadsheet then adds up the weight of the gear in each category, converts it to pounds, and add each category total to the successive subtotals and grand total. If a user doesn’t have an entry for each type of gear, the line can be left blank or deleted.

Additional lines can also be inserted. This template may provide more built-in analysis than some users will want, but I find it useful to know my base weight for a particular outing or seasonal gear collection, the weight of my consumables (which vary with the length of the trip or the distance between resupply points), and my “skin out” weight.

The second, labeled “my three season gear list 120105” is a sample of the spreadsheet I prepared for a recent 9 day hike on the AT in North Carolina and Georgia, with one resupply point.

If you print out either document, you will see they are formatted to print as two pages side by side on a single landscape-oriented page. If the print is too small that way; you can reformat to print on two portrait oriented pages.

Hoping you find my spreadsheet interesting and that others might be able to use it.

Instructions for Mac Users using NeoOffice:

1. Type in title, context of trip, items and comments. Type over the words items and comments as they are only there to remind you what those columns are for. Then save your gearlist. 2. Type in weights in red area. For example, if only grams are known, type the amount in the red area and press enter. When the ounces conversion appears in column H, type that amount in the red area of column J. This is done so the formulas of columns H and I are not deleted. 3. In the green area type a 1 for each item being taken on that particular trip. Leave the 1 for each category or they will not be seen after the filter is run. 4. Click on the drop down menu of cell A4 and click on 1 to show only the items being taken. 5. While holding down the command key, click on the A, J, and K to select those columns. Under the format menu, choose column and hide to hide those columns from final printing.

Menu – Tool to plan menu selection for backpacking trip. Includes summary of weight and nutritional information. Like in the Pack List, the Menu items are selectable based on Categories from the Pantry. Weight and nutritional information is automatically populated from the Pantry and adjusted for Servings. The Menu weight is carried onto the Pack List as a component of consumable weight. Additionally, there is an input box on the Menu for Food Days.

Gear Closet – Provides an inventory of all available gear for selection in the Pack List. All item descriptions and weights are entered in the Gear Closet page. Weight can be entered in grams or ounces as specified in the Options page. Pound and kilograms will calculate based on the weight entered. The Category column is a droplist of available gear categories which is configurable in the Options page. The Gear Closet has been seeded with about 600 items. Much of this data is from the backpackinglight.com Gear Guide. The Visible column specifies whether or not items are visible in the Pack List dropdown menus when filters are turned on in the Options page.

Pantry – Provides an inventory of all available food for selection in the Menu. All food descriptions, weights, and nutritional information are entered in the Pantry page. All weights entered in the Pantry are in grams regardless of the units specified in the Options page since that is the standard for Nutritional Information labels. Like in the Gear Closet, the Visible column specifies whether or not items are visible in the Menu droplists when filters are turned on in the Options page.

Options – Contains various parameters for the display of information in the spreadsheet. Weight and energy units are selectable. As is the format in which pounds and kilograms are presented. Options are available to limit the food and gear choices available to those specially marked in the Pantry and Gear Closet respectively. Additionally, available food and gear categories are configured in the Options page.

Attached is my planning spreadsheet. As I am in Europe for business, unfortunatly I’m not sure if this is going to make it by the deadline. I tried to calculate the time for end of day in California, but then realized that maybe that’s too late for your timezone. I hope you can accept it.

Some notes to consider: Comments on cells, named cells, and named lists are used extensively. All weights are in ounces unless stated otherwise. Macro Ctrl+ D formatts cell to number with three decimal places. That is the only custom macro.

Thanks for considering my submission,