User talk:Darien

VineSimOO 2.0

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)

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)

VineSim update 2.2a

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)

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?