Planner calender

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

Cheers

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

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

pleb,

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 .

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

Thanks, that should help.

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

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

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

Craig,

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.

Bob

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

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 .

The winery I work at is Te Kairanga our website is prob out of date too. Ill have a play is it ok to increase the size of the planner to span a bigger date range? Most of the other things I have planned I think I can manage but I will definitely call for help if I get stuck. I agree excel is so huge in its power it's scary for someone who is just scratching the surface. Must learn some VB techniques used to use visual studio in vb and c++ but haven't in years normally use excel without much vb is normally enough only when I come up with tricky scenarios I have to call on a wizard like yourself.

I knew it was Te Kairanga, I saw your profile on LinkedIn. I also did some other Googling, and one guy commented that the Pinot Noirs of Central Otago are very good (as I know to my delight), but added that the Pinots of Te Kairanga were also up there, so they must be good :biggrin1:.

You can increase the number of dates, but as I said, first unprotect the sheet, then insert the new columns before the current last column so as to preserve the defined names. Then add the date formulas to the new columns and overwrite the final date formula.