User talk:Darien

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)

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)