How do you use a spreadsheet program to plan for a trip?
What follows are the reader entries for the BackpackingLight.com contest to crown THE BEST GEAR LIST SPREADSHEET.
Rules are simple:
Using a spreadsheet compatible with either OpenOffice or Microsoft Excel, create a spreadsheet "template" or "application" that can be adapted for any season, any trip, that anyone can use to help plan their gear and supplies.
All submissions will be juried by their peers in the BackpackingLight.com forums (forum thread below). Winners will be selected by the BackpackingLight.com staff.
1st Prize: $100 Gift Certificate to BackpackingLight.com
2nd Prize: $50 GC
3rd Prize: $25 GC
Click the thumbnail screen shots to see larger versions.
|Alice P. Neuhauser|
Attached you'll find our version of a Gear Spreadsheet. Please note that this is a modified version of work done previously by Carol Corbridge and others (our sincere thanks goes out to them for their generosity in sharing their spreadsheets...). Also note that this is in no way an ultralight list -- it is a simply a template. As "newbies," we have a loooong way to go to reach our load-reducing goals, but we realize it is both a journey and a process; we'll make progress each & every trip. Hope that this template is of help to others, as Ms. Corbridge's was to us.
Attached is my submittal for " THE BEST GEAR LIST SPREADSHEET " contest.
The attached spreadsheet is quite rough, but I don't have time to pretty it up. I'm not intending to win, just thought I'd share some ideas that others may not have thought of.
Attached is my submission.
| David King|
Download spreadhseet #1, Download spreadhseet #2, Zip file containing David's spreadsheets
Here's the equipment list I've developed for my own use. I can dump everything I own (or want) into the list and the subtotal it for specific trips and uses. I've included one with all my stuff in it and one with most things removed so that it can serve as a template. Weights where shown are real. It utilizes several features hardwired into Excel:
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.
Download spreadhseet #1, Download spreadhseet #2, Download spreadhseet #3, Zip file containing all David's spreadsheets
Here are my entries for the spreadsheet contest. There are 3 excel files included...
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.
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.
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 :)
Download spreadhseet #1, Download spreadhseet #2, Zip file containing all David's spreadsheets
Attached is a copy of the gear list spreadsheet I have developed for my own use and as an entry in the contest. Two files are attached: one is blank (except for some sample data in the Pack tab) and ready to be downloaded and used by someone else. The other one is already populated with my own gear data and most recent selections. This should serve as a sample of how anyone can fill in the blanks with their own gear and weight data, make selections, create a printed report, etc.
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.
Attached is my submission for the BPL Gear List Spreadsheet CONTEST.
Attached is my entry for the gear list contest. This is a simple Excel spreadsheet that utilizes the Excel pivot table function to summarize and tabulate the gear data.
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.
Attached is an entry for your spreadsheet contest.
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.
Real basic but works well for me. I just rename in for the trip and edit away. I really like the bar chart to give me a visual of were the weight is going. The password to remove protection is, what else, backpack.
Cheers and thanks for the contest.
Download spreadhseet #1, Download spreadhseet #2, Zip file containings Dick's spreadsheets
Thank you the contest provided the incentive to update and add to my existing list.
I am looking forward to seeing new ideas.
|Vick Hines and Joshua Mitchell|
We (Vick Hines and Joshua Mitchell) are submitting our "Gear Decision Matrix" spreadsheet for the spreadsheet contest.
Details / Introduction have been included in the first tab of the spreadsheet
Download spreadhseet #1, Download spreadhseet #2, Zip file containing all Don's Spreadsheets
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.
I dont know if this is what you are looking for, I have never used spreadsheet software before, but here you go.
Download spreadsheet template #1, Download spreadsheet template #2, Zip file containing both John's files.
Attached is my submission for the contest in template files for Excel and NeoOffice (mac version of OpenOffice) [Editor's Note: An X-Windows implementation of OpenOffice also exists for the Mac OS X if you would rather the native X application instead of the NeoOffice Java counterpart]. I have no idea how it will act in Excel, but it works great in NeoOffice.
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.
Download spreadhseet, Zip file of Meir's spreadhseet
Pack List - Gear list based on the 3-season checklist at backpackinglight.com. Includes a summary for base pack weight and full skin out weight. Food weight is carried forward from the Menu. The Categories column is a droplist of available categories from the Gear Closet. The Item column is a droplist based on the Category selection. Items in the droplists can optionally be filtered to include only items specially marked in the Gear Closet. Weights are automatically populated from the weights in the Gear Closet and adjusted for Quantity.
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.
Hello fellow backpacker,
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,