Display Avatars Sort By: Date (Chronological) Date (Reverse Chronological)
 paul johnson (pj) - F Locale: LazyBoy in my Den - miss the forest Re: multiple weight columns on 11/23/2005 11:38:14 MST 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 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 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 After wasting some time reading manuals, Google found the answer here: http://www.ozgrid.com/VBA/run-macros-change.htmThis 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 IfEnd SubIt'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 , 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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)