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.

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.

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.

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.

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

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

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.

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

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

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.

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 ...

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

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

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

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.

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.

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

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.

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.

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)