BPL Gear List Spreadsheet CONTEST
Display Avatars Sort By:
paul johnson
(pj) - F

Locale: LazyBoy in my Den - miss the forest
Re: multiple weight columns on 11/23/2005 11:38:14 MST Print View

Bob,

off the top of my head, w/o trying this, my guess is that you'll probably get a "circular" reference error. at least one cell/column will need actual "values" vs. formulas. the other cells/columns can then apply formulas to the values. furthermore, if i understand your question correctly, if you type a value (the weight of a piece of gear in this case) into a cell, it will overwrite the formula the was prev. in that cell.

Edited by pj on 11/23/2005 13:31:38 MST.

David Lewis
(davidlewis) - MLife

Locale: Nova Scotia, Canada
Re: Re: multiple weight columns on 11/23/2005 14:30:54 MST Print View

Hey Bob...

Paul explained this very well... but here is another way of thinking about it... Essentially, what you're talking about would require a cell to have a split personality... being sometimes an input (weight of the item) and other times an output (converted weight of the item). I've seen it done with conversion software utilities of course... but I don't know how you'd do that with a spreadsheet. That's why I've created two version of my spreedsheet... one that uses ounces as the original values and one that uses grams as the original values. All formulas are then based on those values / inputs.

Edited by davidlewis on 11/23/2005 14:38:43 MST.

Douglas Frick
(Otter) - MLife

Locale: Wyoming
Re: Re: Re: multiple weight columns on 11/24/2005 08:50:03 MST Print View

That's the real stopping point I've found in turning my gear list from a dumb spreadsheet to a real app. It should be possible to have both a value and a procedure associated with a cell--that's what "objects" are all about. But I haven't touched VBA for years, and I'm not sure I want to start in again now :) I just enter my weights in ounces and let the pounds and grams columns calculate themselves. Checkboxes are something else that could also be added to construct packing lists, but they are either ON or OFF. My solution is to make a Quantity column, which has the advantage of allowing me to indicate carrying more than one of a particular item (e.g., batteries), which a checkbox doesn't do well.

Hmmm...maybe I will look into VBA again.

Douglas Frick
(Otter) - MLife

Locale: Wyoming
Re: Re: Re: Re: multiple weight columns on 11/24/2005 10:49:39 MST Print View

After wasting some time reading manuals, Google found the answer here: http://www.ozgrid.com/VBA/run-macros-change.htm

This is the VBA code that now updates my "grams" column when the "ounces" column changes, and v.v. Column F is "ounces" and column H is "grams".


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'do nothing if more than one cell is changed or cell was cleared
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
'if changing ounces, then update grams
If Not Intersect(Target, Range("F2:F65536")) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
Target.Offset(0, 2).Value = Target.Value * 28.35
Application.EnableEvents = True
On Error GoTo 0
End If
End If
' if changing grams, then update ounces
If Not Intersect(Target, Range("H2:H65536")) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
Target.Offset(0, -2).Value = Target.Value / 28.35
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub


It's not very elegant and can probably be cleaned up, but it works. Unfortunately, VBA is Microsoft Excel specific, and the updating didn't occur when running the spreadsheet in OpenOffice.org. I guess, in that environment, one of the columns will need to be a formula based on the other.

In my spreadsheet between columns F and H is column G, "pounds". The formula in this column is (from cell G2):

=IF(F2="","",IF(F2>16,INT(F2/16)&"# ","")&IF(MOD(F2, 16) < 10, " ","")&FIXED(MOD(F2, 16),1,0))

This displays a nicely formatted pounds column (e.g., "Black Pine 0F sleeping bag" is "8# 4.0"). I'm too lazy to write a parser to make a pounds-column entry update the ounces and grams columns.

The other day I added outlines with subtotals, so now I need to clean up and reorganize my list into groups.


P.S. Arrrrgh! The column update DOESN'T always occur if you change a value and use an arrow key to move to a different cell. You have to press <enter>, move back into the cell with an arrow key, or click the cell to get it to update. Sigh. Typical Microsoft.

PPS. Except when it DOES. Also, it thinks fomulas are numeric, so it will gladly stomp subtotals and other calculations if you even click on the cell. I'm not sure this auto-update feature is really worthwhile!

Edited by Otter on 11/24/2005 14:09:40 MST.

Bob Gabbart
(bobg) - F
Please extend Spreadsheet deadline on 11/29/2005 11:33:33 MST Print View

Ryan,

Is there any chance that you could extend the deadline for spreadsheet submission to Dec 5th? It would be nice to have one more weekend.

Thanks,
Bob

Jim Colten
(jcolten) - M

Locale: MN
Re: Please extend Spreadsheet deadline on 11/29/2005 12:22:55 MST Print View

If there is no extension, is the deadline at 00:00 or 23:59 on 12/1?

John S.
(jshann) - F
Re: Please extend Spreadsheet deadline on 11/29/2005 18:43:06 MST Print View

I think the deadline should be extended at least one week because of the holidays. You might get alot more people finding time on their hands to compete.

Phil Barton
(flyfast) - MLife

Locale: Oklahoma
Re: Please extend Spreadsheet deadline on 11/29/2005 18:51:05 MST Print View

Of course, [big sarcastic, self-critical wink] this sounds like every software project I've ever been around. Sure, we need another week.

Bob Gabbart
(bobg) - F
Plea for RJ - Please extend deadline on 11/30/2005 08:36:42 MST Print View

Ryan,

One last plea to extend the deadline till Monday Dec 5. Also, can you clarify if the deadline is at 00:00 on the deadline date or 11:59.

Thank you,
Bob

Richard Matthews
(food) - F

Locale: Colorado Rockies
not fair on 11/30/2005 11:02:12 MST Print View

Extending the deadline is not fair to the people that might have sacrificed time with their family to tweak their spread sheets.

The IRS convention would indicate the email has to be sent 12/1.

David Plantenga
(IndianaDave) - F
Gear Spredsheet Contst ... on 11/30/2005 13:31:11 MST Print View

Hey Kids,
YIKES ... this one's tooooo complicated for me.
I am "very" interested in the Winner's submission tho.

Maybe the most EXCITING Contest confusion was ... When/What time does it end? Love it, Love it, Love it ...

David Lewis
(davidlewis) - MLife

Locale: Nova Scotia, Canada
Re: Gear Spredsheet Contst ... on 11/30/2005 15:16:35 MST Print View

Since the rules never specified time of day, I assume anytime on the 1st would be fine... anytime before 23:59:59.99 :)

John S.
(jshann) - F
Re: BPL Gear List Spreadsheet CONTEST on 12/01/2005 17:27:07 MST Print View

The mac users should definitely check out the verson of OpenOffice called NeoOffice which is a nice user interface written over OpenOffice.

http://www.planamesa.com/neojava/en/olddownload.php

Bob Gabbart
(bobg) - F
Spreadsheet deadline is here! on 12/02/2005 12:12:44 MST Print View

So the spreadsheet deadline has come and gone. How many entrees were submitted? When do we get to see them? What clever things did people come up with? Is anyone else exited about this??

Bob

Ryan Faulkner
(ryanf) - F

Locale: Mid atlantic, No. Cal
Re: Spreadsheet line is here! on 12/02/2005 12:16:34 MST Print View

I guess I am excited. I submitted a spread sheet and want to see how I compare. I in no way beleive that I will win, because I have never used spread sheet software before and had to figure it out on my own, so I probably did not do a fantastic job, but oh well.

Richard Matthews
(food) - F

Locale: Colorado Rockies
Two contests? on 12/05/2005 11:24:57 MST Print View

How about a set of prizes for the best spreadsheet technique and a second set for gear variety? The second set might be called the Gear-Ho prizes.

Deadline + 4 days and no news. There is probably a very full email box and a very overwhelmed person.

Bob Gabbart
(bobg) - F
time spent on spreadsheet on 12/05/2005 11:41:23 MST Print View

Just wondering how much time people spent preparing their Gear List.

Richard Matthews
(food) - F

Locale: Colorado Rockies
time? on 12/05/2005 11:54:25 MST Print View

Bob,

The existing spreadsheets needed some updating that I planned to do someday. About 4 hours updating.

About 2 hours adding features for the contest.

Joshua Mitchell
(jdmitch) - F

Locale: Kansas
Time Spent? on 12/05/2005 12:11:32 MST Print View

Hmm... don't know, a few minutes here or there... Vick and I co-submitted, so a lot of the time was spent waiting for emails to go back and forth.


(Anonymous)
Re: Spreadsheet deadline is here! on 12/05/2005 12:24:57 MST Print View

I think Ryan is probably so freaked out by how awesome mine is that he is contemplating calling off the entire contest because it's hands down mine. I am still trying to think what I want to buy with my hundred buckaroos. Any suggestions?....

Anon
(just kidding around)