Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Planner calender

  1. #1

    Planner calender

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, I have a table which basically shows the tanks we have in our winery, I have another sheet that I can input Batch name, date harvested, days needed in Tank 1(T1) and days needed in Tank 2(T2) ie. so I can harvest into one tank and then rack or press into another tank. Also data about how many days in T1 and how many in T2.

    I would like to automatically fill the planner table with the batch and tonnes into the appropriate cells when the tank is being used.

    i tried vlookup but it comes unstuck whenthe tank gets used twice. It finds the first instance in the input table and fills the cells appropriately for Tank 1 but if I try and use the tank again it wont find it. Also havent got around to filling Tank2 data.

    I am guessing I will need a macro for this If anyone can help and if they are in New Zealand I will get them a bottle of good wine for their troubles, sorry for anyone outside of NZ as it gets a bit tricky sending wine over seas.

    Thanks in advance especially those that cant take me up on a free bottle of wine


    I have attached my first attempt so you can see what I am trying to achieve
    Attached Files Attached Files

  2. #2
    Sorry just looked at my spread sheet, the first "days" collumn relates for days in Tank1(T1) and the third "days" is for Tank2(T2) the second days collumn is a cockup and isnt needed

  3. #3

    You have lost me on exactly what is going on here. I think you put details in the Fill Dates sheet and then want to show those details on planner in another form? But I cannot understand your VLOOKUP formula(s), or what you would expect to see, why E. It would be best if you showed your workbook with a worked example, no formulae, just showing what you want on planner for whatever parameters (tell us which parameters are used).

    I will be in NZ later this year, I would love a good bottle, so I am keen to solve this for you .

  4. #4
    Basically we have slot of tanks as listed in the planner sheet I.e 12/01 or A or 5/01 we then have 90 odd different batches of grapes that need to go through 1 tank for ferment then it is moved to another tank. Each tank may get used for 3 or more different batches of grapes. I had it set up to show E if it was empty. The input sheet will have all of our grape batches on it and we can then plan when each batch will be picked and what tanks it will use in the process. The planner will give an overview on which tanks will be used and when.

    The vlookup formula works but inly for the first instance that the tank is used so if we plan to use a tank for a batch then it gets emptied so we can use it for another batch it will only find the first instance when the tank is used and not any subsequent times it is used.

    Tomorrow I will make a mockup of how I want it to look and attach it with no formulas besides basic date formulas etc

    Thanks heaps

  5. #5
    Thanks, that should help.

    Presumably you are in the South Island, around Wanaka/Queenstown? My daughter is in Christchurch.

  6. #6

    Spreadsheet workup

    Have made a mockup with a few lines of data and have attached

    I am in Martinborough which is just out of Wellington. If your up this way you will have to call in and get a tour and tasting especially if your here from march to june you will see the big game of tetris we play every year at harvest and how this planner will come in handy.

    I have put a few comments in cell as well as some open comments, hopes this makes sense

    Cheers craig
    Attached Files Attached Files

  7. #7
    Oh I changed the format so its easier to follow i.e. the batch name in collumn 1 in the fill dates sheet. this is how I would prefer it in the final version I only changed it so that vlookup formula would work

  8. #8

    Attached is a first cut.

    A few points:

    - I did it with VBA even though I think I could have done with formulas, as VBA will allow easier extension. The macro is triggered by a button on the 'Fill Dates' sheet. This does mean that you have to enable macros when you open it, and that if you change the start date, the plan will not reflect that change, you will have to go to 'Fill Dates' and run the code again

    - I have protected the Planner sheet. Changes should only be made to the source in 'Fill Dates'. Columns A:C are unlocked so that you can set the start date and change the tank names, but the actual plan is locked

    - I have created some defined names. If you enlarge the plan, make sure that you insert the extra rows and columns before the current last row or column so that the names automatically update (you will need to unprotect the sheet before inserting rows or columns, there is no password)

    - I have given the worksheets codenames for coding ease, I can reference that codename in the code

    - I have coloured the batch name/id on the 'Fill Dates' worksheet and I use that colour when filling out the plan, this allows you to determine the colours in the plan, not hardcoded

    - I have removed your starting colour on the 'Fill Dates' worksheet because I strongly believe that less is more in visualisation, highlight the things you want to show, don't over-use colour

    I hope this is a good start, but just shout, suggest, if you want to change it or take it even further.

    Attached Files Attached Files
    Last edited by Bob Phillips; 2013-02-11 at 09:52 AM.

  9. #9
    You my friend are a legend, you have definately got yourself a bottle of something special. Let me know when you are in NZ and if in the wellington region make sure you call in for a VIP tour and tasting Thanks so much I would have never got there its been a long time since I pretended to know what I was doing with VB and never quite transferred any of my limited knowledge to excel.

    Thanks heaps, send me a message when youre in the land of really shit cricket players and will organise your wine

  10. #10
    It's a pleasure Craig. I love wine, and I love Excel, so what could be better than a wine oriented spreadsheet . If you have any other things you would like to solve using Excel, just holler, Excel is so versatile it is scary.

    Assuming we do make it again this year, it will be towards the end of the year as my daughter is doing her PhD at the University of Cantebury, and she completes it then. I hope we get an opportunity to pop over and see your operation, we visited the Carrick winery at Bannockburn in 2011 and had a lovely day. BTW, just been on your site, and the tasting notes pdf is a bit dated.

    As for your cricket team, its all cyclical. I still remember when you had a great team and were tonking us. You should be more concerned that we just beat you at rugby .
    Last edited by Bob Phillips; 2013-02-11 at 11:10 AM.

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts