User talk:Darien

Flavor Chart

If anyone is interested, I'd love to add their flavors to the flavor chart. Just say here what flavors you have and I'll add them to the Flavor Chart when I get a minute. The intention for this is for others to be able to see who has what flavor and contact them with trade/information.

For those curious, its just an excel spreadsheet that I edit. I can quickly insert a row, add Yes/No, and have it updated within 1-5 min. I like things to be quick and painless. :)

BarrelSim

File:BarrelSim v1a.zip

I did some formatting on the Barrels page. Let me know what you think so far.

I also moved the worksheets list to the "instructions" page, assuming the user would want the same list for each barrel tab. Once everything else is set, we'll probably want to put a lot more instructions there, or even automate the process of extending the Name range if the user adds more rows.

--Shebi 19:38, 18 February 2009 (EST)

I'm still trying to figure out a user-friendly way for the barrels. The only thing I can think of doing is putting instructions on how to insert a row into the list manually. You can select 1 of the rows in the list and do Insert -> Rows.

Also thought about maybe a few fields at the bottom or somewhere where the user could manually put in numbers to be included into the math in case they mess up on a vine or get the grapes from somewhere else. What do you think about that?

--Darien 20:32, 19 February 2009 (EST)

File:BarrelSim v1b.zip

Ok Shebi, I have updated the BarrelSims again. I only did it to a few sheets so I could put it to use tonight when I do some barreling. I added a clear worksheet macro that will reset all values back to 0/default state. I know there is some formatting that needs to be done so have at it! I wanted to let you take a stab at it and also I wanted to give it a try to see if there is anything missing or could be put to good/better use elsewhere. Let me know what you think.

Thanks, --Darien 19:54, 23 February 2009 (EST)

I've put this project on hold for the time being because in its current state, I can't figure out a fast efficient way to work this in my wine workflow. --Darien 18:43, 2 March 2009 (EST)

VineSimOO 2.0

File:VineSimOO v2-0.zip

Alright Shebi, its up there. If you want to load OO and mess with some of the formatting have at it. Its exactly like Excel but different. I don't like how it handles the conditionals and I think that is what is slowing the document down when you swap back and forth from excel to scalc. That and the nested functions. Anyway, as far as I'm concerned its good to go. --Darien 18:51, 4 March 2009 (EST)

VineSims Excel

VineSim update 2.1

File:VineSim v2-1a.zip uploaded. Formatting fixes, one formula error fixed. I didn't put the Bold feature in that you mentioned, and I'm not sure I understand what the Y and N settings are for, so those instructions aren't complete. --Shebi 18:15, 19 February 2009 (EST)

I guess we could probably take the Y/N out. It was my solution to keeping up with the tend until I thought of the C. I'll try tending the vines tonight without em and see if it makes a difference. It does look kind of busy with them on a filled out sheet. I got the favored tend bolded on each table and was trying to do it before you grabbed the file but I see I didn't make it in time. :) We can add it for another release. For personal preference I may have to change the white background. Its blinding me, of course it could be this bright monitor I use at work. I'll give it a go when I get home from work and see how it works out. Thanks again for such a fast fix. This will give me a chance to update my main vineyard spreadsheet. I'm still using one of the older spreadsheets for it.

--Darien 18:56, 19 February 2009 (EST)

I haven't updated the download page, or replaced version 2.0 in the official release, so there's still time to add the Bold feature if you like. In the meantime, I'll play around with the background color a bit too. --Shebi 19:42, 19 February 2009 (EST)

Ok, soon as I finish my nightly chores here, I'll finish copying the tables into the new spreadsheet and upload it. Should be in the next 30 min or so. --Darien 19:55, 19 February 2009 (EST)

Here it is. The white isn't so bad now. I messed up and looked at the document after I had spent the past 2 hours working on the AS/400. Black screen w/ green text. Also changed the tend to C/D for Current/Done. File:VineSim v2-1b.zip --Darien 20:25, 19 February 2009 (EST)

VineSim update 2.2a

Updated Vinesims again with a few changes listed on the page with the request for extra tends: File:VineSim v2-2a.zip --Darien 18:40, 2 March 2009 (EST)

I did a fairly significant reworking of 2.2, after I found an easier way to do the lookups (using INDEX and MATCH). Also added 5 more yard tabs and the extra tend rows, and repeated totals at the top of the yard tab. It could definitely use a going-over by someone else to check for bugs before we post it as 'official.' File:VineSim v2-2.zip --Shebi 14:26, 6 March 2009 (EST)

Wow, looks great. I kept reading how Index/Match is more efficient, but couldn't figure out how to use it. Kudos to you. That should also clean up and fix all of the slow issues I was having with the Open Office version. I'm slowly relearning VBA (took a class on it in college 6 years ago and I remembered nothing from it). I have a macro that cleans the worksheet and one that fills out the worksheet with your favored tends which I'll work in as buttons. I work in IT and its a quiet day here. Our philosophy on Friday is "If it isn't broken don't touch it!" so I should have some time to tinker with it. Great work! --Darien 15:12, 6 March 2009 (EST)

Found one error, so I'll replace the file. I didn't specify the type of match, so it was stopping at SmTV for some reason, rather than getting exactly SmTL (and same for SvTV/SvTL). I've added the '0' parameter to the MATCH so it now is exact. --Shebi 15:19, 6 March 2009 (EST)

Ah ok, that makes sense. If there isn't a type of match specified it defaults to '1'.

"The Match function will find the largest value that is less than or equal to value. You should be sure to sort your array in ascending order. If the match_type parameter is omitted, the Match function assumes a match_type of 1."

Apparently it was finding something less than and using those values?

Yup, I made the assumption (oops) that most folks would want to default to exact matches. Silly me! :D

Where should I put the buttons for the macros? --Darien 17:57, 6 March 2009 (EST)

Hmm. Either a) put them both on the Instructions tab somewhere (perhaps in row 2 starting around column J or K?) or perhaps just add a new worksheet between Instructions and Tend Summary, called "Macros" or "Controls"? What do you think? I also had a macro in there for toggling all sheet protection at once (without a password), if you want to create a button for that too....

I saw that, nice. These macros will be for each sheet. and I could make one that fills in/clears every sheet, those would be fine on the instruction page. Gotta problem here I'm tinkering with. If the Tend is selected and the State isnt on a tab the values come up as #N/A. and the =IF(ISBLANK(OR(B6,C6)) isn't working.

Oh and I'm curious, whats the { } around each formula for? When I try to use it, it doesn't show up as a formula.

The {} are for array formulae, to get the value of something rather than the location. We may not really need them here, I was trying to get a single function that would fill out the entire row but ended up just modifying the index for each column instead. You get them by hitting ctnl-shift-enter when editing the cell. Regarding the bad reference, no clue why that's happening, except that Excel is always quirky. I'm trying to figure out how we can check if the cell value is blank, since the cell isn't empty (it has that lookup formula in it).

Ok, thanks. As for figuring out. I know you spent a lot of time whittling the formula down, but this seemed to be the only way I could get it to work.

=IF(OR(AND($B7<>"F",$B7<>"MG",$B7<>"R",$B7<>"Sa",$B7<>"Sm",$B7<>"Sv",$B7<>"W"),ISBLANK(C7)),

0,INDEX($V$7:$AB$73,MATCH($B7&$C7,$S$7:$S$73,0),1))

I was trying to figure out a way to get it to check the StateS list, but it kept returning Sv as the value.

This might be a little cleaner:

=IF(($B6=" "),0,(IF(ISBLANK($C6),0,INDEX($V$7:$AB$73,MATCH($B6&$C6,$S$7:$S$73,0),1))))

This is a nested IF - if the first IF evaluates to false, it goes on to the second one (the old formula).

Then we should also adjust the conditional formatting to: =OR($B6=" ",ISBLANK($C6)) to hide the zeros.

Ah.. so it needed a space in there. That works w/o the extra nested function... =IF(OR(B6=" ",ISBLANK(C6)). It was confusing me how the nested IF() would work and not in the original IF(). Well, do you want to change them or I can try to take a little time to fix it this weekend? My nightly routine stuff is about to begin.

Sweet, even better without the extra IF. Yeah, that only works because the lookup in column B explicitly sets it to a space if there's nothing in the Tend Summary. Sounds like we're getting close. I can work on it tomorrow, would you rather have me fix the sheets then toss back at you for the buttons, or vice versa?

You working on it first works for me. I'll need a little time to tinker with the formula for a macro to fill out the favored tends on all of the tabs. Do we want to release this w/ the Appreciation tab filled or wait until the next vine in NP is unlocked?

NP got Appreciation too (I suspect because they unlocked too soon after SA). Let's add the lastest Q #s for appreciation as the very last thing, if the table is close to done we can wait, otherwise we'll just send it out with the partial data. BTW, the copy/paste thing was faster than I thought, so I'm uploading a revised version now. Tinker away with it. :D

Great, I finally got a few vineyards with Appreciation set up, I'll work on getting the wiki tables updated. Next will be updating OO. Hopefully all I have to do it on it do a find/replace ! for . now that the nested functions are gone. I'm not sure how/if OO uses VBA. It does give you several options for different code such as Python.

VineSim update 2.2b

File:VineSim v2-2b.zip

Found a problem. When vigor gets to 0 it doesn't go below 0 like it used to. I've got the VBA code worked out. I've only done the 1st vineyard with the buttons. I wasn't sure where to put them so I figured I would get the first page set up and then let you decide from there. I also created the 2 buttons on the Instructions page at the top. I didn't fix the vigor problem. The State in tending 35 isnt showing up in the tabs. I'm fixing that then I'll upload the new version to go from there.

To move the buttons (in case you're not sure how)

• Go to View -> Toolbars -> Control Box
• Click on the Design button (top left icon in toolbox)
• Click/Drag/Resize button from there. You can double click on Icon to see its code.
• Change the button text in Caption with the code opened in Properties Window.
• Change font size by selecting the font with the code opened in Properties Window.

Vigor problem is fixed (hah, you had the very last cell in column R set to same equation as the other totals) and tend 35 is fixed. All that is left is for you to look over and decide how you want the buttons then I'll finish the other 24 buttons, or you can if you prefer. I also added shortcut keys to go to next and previous worksheet (Ctrl + k; Ctrl + l). The default (Ctrl + PUp & Ctrl + PDown) doesn't seem to like protected worksheets. --Darien 20:24, 10 March 2009 (EST)

File:VineSim v2-2b2.zip

I've also got a macro in to where you can update your tend sheet from an old worksheet. I'll get the file uploaded with that shortly. The way it works is you open the new VineSims_2.2 and your old worksheet. You select the Tend Summary on your old worksheet and run the first macro in the macro list... A_Copy_Tends and it should do the rest from there.

File:VineSim v2-2b3.zip

Alright.. 2b3 is posted with changes. I added all the buttons and the macro for copying old spreadsheets. Now we just need instructions on the instruction page. --Darien 16:48, 12 March 2009 (EST)

Ok, so very sorry I've not gotten back to you on this. I had "use it or lose it" vacation, then a birthday, then a sick cat... excuses excuses! I'll do my best to take a look tomorrow. --Shebi 19:41, 18 March 2009 (EST)

Hah, understandable. I was just about to try messaging you tonight because I've had several folks asking me when it would be updated with the new vine. I've been using it on all of my worksheets recently and I have yet to find a bug.

VineSim update 2.2c

File:VineSim v2-2c.zip

I added confirmation dialogs to the "fill all" and "clear all" buttons, and tweaked the "fill favored tends" one to stop when the vigor dropped to 0 or below. I also did some minor style updates (pretty button colors). I couldn't get the macro for importing old data to work, not sure what steps I'm missing. No new instructions yet, other than the dialogs - I keep starting to add a whole new "macro tools" sheet then end up removing it again. If we add buttons for import old data or for any other macros, maybe it'll make sense to have one. Otherwise, once I understand what the steps are, I can add copy to the Instructions tab. :) --Shebi 15:41, 19 March 2009 (EST)

Nice, the document must currently be named VineSims_v2-2b.xls or whatever it says in the macro.

If the spreadsheet requires to have a specific name im going to have issues since I have multiple files. I currently have 1 spreadsheet for each group of vineyards I have. Im sure you werent thinking that people only had 25 vineyards total. --Akmenotep

(Was a misunderstanding, all taken care of) :) -Darien

Steps to take are

• Open VineSim_v2-2b2.xls
• Open the older spreadsheet
• go to the Tend Summary on old spreadsheet
• run Macro "A_Copy_Tends" - this will create a worksheet and copy the tends to that worksheet
• Once Macro A finishes it will run macro "B_Copy_TendSummaryValues" - this copies the TendSummaryValues tab to the new worksheet "VineSim_v2-2b2.xls" (this is what will need to be changed on the newer versions) I have an idea on how to change it but haven't sit down to figure it out yet. If you can figure out the command to tell you the file name then you could create a variable using that as a string then plug the variable in to get it to work in theory.
• Then Macro C runs "C_Copy_Updated_TendSummaryValues" - this takes the values on the copied spreadsheet and copies them to the Tend Summary on the new worksheet.

With spring/summer coming up I won't have as much time to sit down and work on this anymore. Between coed softball, a wedding and extra web design jobs, I'm gonna pretty much be press for time. So once we get this worked out I'm not sure how much farther I'll be able to take this outside of minor tweaks and fixes.
--Darien 17:09, 19 March 2009 (EST)

Is there a way to ask the user to indicate the old workbook and then open to its Tend Summary tab during the macro? If so, we could launch the update macro from a button in the new version. (If not, I think I can safely write up the instructions now, though I may put them on the wiki page rather than in the spreadsheet, since I can be more verbose there.) Also, should we delete the TendSummaryValues worksheet once everything makes it onto the regular Tend Summary tab? AWESOME work, by the way.

Totally understand about RL taking precedence, especially if that's YOUR wedding! We're pretty close to running out of things to add anyway, as far as I can tell. :)

--Shebi 18:00, 19 March 2009 (EST)

For asking the user, that starts getting into more advance scripting and I wouldn't know where to begin with my current knowledge. It could be something as simple as a &_ or a form for all I know. I have just started dabbing into excel forms. I even tried having it set to check for data in a cell to find the document name. You can do "filename" in a cell and it will display the filename with the rest of the directory tree -- problem I was running in to. Here is a link to what I am talking about: "filename". Like I said before it could be something very simple that I'm over looking.

At the end of the macro, it should delete the value sheet from the new workbook. It should also ask you a yes/no question about deleting a sheet filled with information. (I'm not sure on the option to auto-answer that question.) The user can close the old worksheet without saving in order to get around deleting the tab, or we could easily add that into the macro -- but they will get the yes/no question and will stop the macro until a user answers it. I tried to stay away from that. If a user messes up, they can always close without saving and do it over again (had to try that from personal experience). The only thing I didn't do with the copy/move is in the first copy macro "A", I did not take into count the new 5 vineyards & 36-50 tends. So for future releases, we would have to add the copy for the last 5 vineyards and tends. I did go ahead and include those in the "C" macro.

Thanks, you have done an awesome job as well. I've learned quite a bit from this experience with excel, hopefully you have as well, and yes the wedding is for me. :)

--Darien 20:43, 19 March 2009 (EST)

I just now realized I never really answered your initial question. In order to get the new worksheet to automatically go to the old worksheets Tend Summary tab, the new worksheet would have to know the name of the old worksheet. That is where it would require some method such as a prompt coming up letting you select a file on your computer.

Is there anything else you are waiting on from me?

--Darien 18:02, 23 March 2009 (EST)

VineSim update 2.2d

File:VineSim v2-2d.zip

Ok, I went a little macro crazy, but I think it was worth it. Please take a look and do some testing if you have the time! Thanks again. :D --Shebi 14:53, 24 March 2009 (EST)

All I can say is Wow... looks great. The only bug I could find is the clear tend button doesn't work on tab 5, and this may be intentional, but your name is listed in some of the macros.

All I was missing was the global variable. I for the life of me couldn't remember how to create those.

Again, excellent work. The only other thing I could see adding is maybe a note to hit 'esc' key to interrupt/end the macro.

--Darien 18:48, 24 March 2009 (EST)

I forgot to add one thing on the list for you to fix... There is still information filled in on the charts and favored tends tab. --Darien 02

01, 25 March 2009 (EST)

D'oh, thought I'd wiped everything. Silly monkey me! :D

Point System

I seemed to figure out a decent way of coming up with the points that worked for helping figure out the tends. Outside of that I'm not sure how it could be useful, but I thought I would share in case you get a few ideas and can build on this.

I started by ranking by importance the stats. For drinking I personally think in this order: Quality, Grapes, Sugar, Acid, Skin, Color. So starting from the top I assign them Q - 6, G - 5, S - 4, A - 3, K - 2, C - 1.
Pretty much the next formula is those stats multiplied and added together to give you a number.
If you plug this varible into AD7 on any of the tab worksheets and copy down you will see where I am getting at: =(V7*3)+(W7*1)+(X7*5)+(Y7*6)+(Z7*2)+(AA7*4)
Then i cell AE7, copy this formula:=AD7/(AB7-AB7-AB7) and run it down the list.
This formula takes the total number and divides it by the vigor (i couldn't remember how to change a neg to a pos. - must have slept some since high school). This final number should give you a good indication of which tend is possibly best. You could set/change these numbers in the vine tab; Appreciation, Amusement, etc. maybe one for drinking and one for tannin and one for whatever else would be required. Anyway, this seemed to work in the initial testing for me. It picked what are already my favored tends for Amusement.
Your thoughts? --Darien 17:27, 19 March 2009 (EST)