CellarTracker Main Site
Register for Forum | Login | My Profile | Member List | Search

Cellar Map - new spreadsheet

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
Users viewing this topic: none
  Printable Version
All Forums >> [Cellar Talk] >> CellarTracker Support >> Cellar Map - new spreadsheet Page: [1] 2 3 4 5   next >   >>
Login
Message << Older Topic   Newer Topic >>
Cellar Map - new spreadsheet - 12/27/2017 12:12:32 PM   
anevard

 

Posts: 109
Joined: 6/5/2006
Status: offline 		WIth some free time on my hands during the holidays, I finally got around to developing a spreadsheet that shows my cellar layout, empty bins etc. I know that there were some previous ones developed in the past, but I never managed to get a hold of those and they are quite a few years old now. My sheet is based on the original CT webquery.xls using the Individual Bottle xlquery.asp allowed by the CT developers.

Once the cellar layout is setup the sheet is pretty automatic.



The Login and Individual Bottles tabs are the standard ones from the original CT webquery.xls. Clicking on the Individual Bottles tab automatically refreshes it from CT.

The Check Bins tab allows the user to enter bin names that need to be flagged for some sort of verification.

The Bins tab (W bins in my case) is where the layout is shown.

The layout can be seen with bin names



or with bottle counts



For double depth bins (Front and Rear) there is a depth chart showing superimposed available space



Each bin's maximum bottle count is entered during setup so that actual bottle counts can be compared to the maximum for each bin. The bin can be flagged if there are more bottles than the max.



And lastly, double clicking on any bin will show the names of the bottles in that bin




This sheet has been really helpful with my cellar management. I just found 2 missing bottles!

I am happy to share this with the community and, time permitting, happy to setup your cellar layout in it.

Your comments, suggesstions, etc., would be appreciated.

Cheers,


_____________________________

Andrew
Post #: 1
RE: Cellar Map - new spreadsheet - 12/27/2017 12:49:11 PM   
cigar52

 

Posts: 345
Joined: 8/15/2013
From: California... now Sarasota, FL
Status: offline 		Althought I have used a paper with my cellar in a graphic form for the past 10 years (which has worked fairly well) I love the automation available for bottles removed without annotation on my paper graphic.

Great concept ... please keep us informed.



< Message edited by cigar52 -- 12/27/2017 12:50:33 PM >

(in reply to anevard)
Post #: 2
RE: Cellar Map - new spreadsheet - 12/27/2017 1:02:08 PM   
anevard

 

Posts: 109
Joined: 6/5/2006
Status: offline 		Thanks Cigar!

I gave up on graphing it years ago, but with the need to re-organize and too many sticky notes, I finally put my head to this. It wasn't as hard as I thought it would be.

If you track your bins on CT with unique names for each bin, it is pretty easy to make this work. When you feel like putting away the graph paper, let me know.



_____________________________

Andrew

(in reply to cigar52)
Post #: 3
RE: Cellar Map - new spreadsheet - 12/27/2017 2:40:36 PM   
cigar52

 

Posts: 345
Joined: 8/15/2013
From: California... now Sarasota, FL
Status: offline 		I would be willing to try dependent on startup costs. Computers and friendly programs are a plus.

(in reply to anevard)
Post #: 4
RE: Cellar Map - new spreadsheet - 12/27/2017 2:59:21 PM   
bacchus

 

Posts: 1136
Joined: 7/25/2004
From: Staten Island, New York
Status: offline 		dear andrew-

have you asked eric to integrate this?

in any event, i would like to give it a whirl.

in addition to multiple bins, i have multiple locations. are you able to accomodate this?

cliff

_____________________________

A Country Gentleman

(in reply to cigar52)
Post #: 5
RE: Cellar Map - new spreadsheet - 12/27/2017 6:33:43 PM   
anevard

 

Posts: 109
Joined: 6/5/2006
Status: offline 		Hi Cigar,

This is just something I put together so I could get some additional functionality from the CT website. As long as I have some free time, I am happy to help other people better their CT experience. No $ involved.

If you want to PM me a bit of info about your cellar layout, I will be happy to see if I can integrate it into the spreadsheet.

BTW this is developed on the latest version of Excel Office 365 but should work on any version from 2010 forward.


_____________________________

Andrew

(in reply to cigar52)
Post #: 6
RE: Cellar Map - new spreadsheet - 12/27/2017 6:38:26 PM   
anevard

 

Posts: 109
Joined: 6/5/2006
Status: offline 		Hi Cliff,

I wish Eric and Dan would come up with something. It seems to be on their todo list for the last 10 years or so. In the meantime, this works for me.

As long as all the bin names are unique, multiple cellar locations are not an issue. If there are repeating bin names across locations, I will have to tweak the code a bit, but happy to try.

If you want to PM me with some info on your cellar, I can see how we can make it work.

Cheers,


_____________________________

Andrew

(in reply to bacchus)
Post #: 7
RE: Cellar Map - new spreadsheet - 12/27/2017 7:31:21 PM   
cigar52

 

Posts: 345
Joined: 8/15/2013
From: California... now Sarasota, FL
Status: offline 		Thanks Andrew.

Will PM in the AM. Not sure how to PM.

Murray

(in reply to anevard)
Post #: 8
RE: Cellar Map - new spreadsheet - 12/27/2017 7:39:15 PM   
CranBurgundy

 

Posts: 8272
Joined: 1/5/2016
From: Philly / South Joizey
Status: offline
quote:

ORIGINAL: cigar52

Not sure how to PM.



EZ PZ Murray - just click on "PM" at the bottom left of whomever's post that you want to PM (circled in yellow below):





You'll be automatically taken to another screen that's ready to enter your message. Just add a title, then your message as if it was a regular post, then hit the "OK" button down below the message you just typed. Voila!!!

_____________________________

Purple Drankin' Cretin.

Vote NO on Proposition S1ct1516 "BAN the CRAN!" this Election Day.

“Let it be recorded: henceforth, December 15 shall be known as 'The Day of Dennis'.” - Prof. Ken "KPB" Birman, 12/17/23

(in reply to cigar52)
Post #: 9
RE: Cellar Map - new spreadsheet - 12/27/2017 7:45:39 PM   
cigar52

 

Posts: 345
Joined: 8/15/2013
From: California... now Sarasota, FL
Status: offline 		Sent PM

(in reply to CranBurgundy)
Post #: 10
RE: Cellar Map - new spreadsheet - 12/29/2017 5:58:20 AM   
cigar52

 

Posts: 345
Joined: 8/15/2013
From: California... now Sarasota, FL
Status: offline 		Thanks for the great spreadsheet.

Works like a charm for replacing my penciled sheet.




(in reply to cigar52)
Post #: 11
RE: Cellar Map - new spreadsheet - 12/29/2017 7:43:56 AM   
anevard

 

Posts: 109
Joined: 6/5/2006
Status: offline 		Thanks Cigar. Glad it works for you, and we saved some trees!


_____________________________

Andrew

(in reply to cigar52)
Post #: 12
RE: Cellar Map - new spreadsheet - 1/9/2018 1:18:16 AM   
jmc167

 

Posts: 81
Joined: 12/30/2015
From: Portland, Or
Status: offline 		All - First off sorry for not following this post sooner, I saw Andrew's post on Christmas Day elsewhere in the Forums and immediately PM'd him and had a working version within hours! My three "stable" lockers at my offsite storage site are 260B, 264B and 265B and I use the JK Adams racking to provide individual slots as that allows me to put one rack in from of an another so they are two deep, which supports my current method of collecting 1 to 6 of any 1 wine at a time. So the majority of my locations in CellarTracker are single bottle bins, which is super helpful for finding that one wine, but allows for mess-ups as well.

Anyway, I finally got down to the cellar last Saturday with my laptop and the program and wow, what a difference the app made! Even though Andrew's app was only showing about 5 things to check (a few 2 bottles in 1 bottle bins and some empty bins that were really not empty) but did I spend some time due to the chain reaction!

Some picks are below and if you want a super simple always updated picture of your cellar - PM Andrew - this thing is awesome!!!

[url=https://flic.kr/p/FbUG7A]

< Message edited by jmc167 -- 1/9/2018 1:24:51 AM >

_____________________________

Just a guy trying to drink as much as I purchase, and losing this battle brilliantly!

(in reply to anevard)
Post #: 13
RE: Cellar Map - new spreadsheet - 1/9/2018 5:56:35 PM   
wine247365

 

Posts: 1009
Joined: 5/1/2012
From: OC, CA
Status: offline
quote:

ORIGINAL: anevard

Hi Cigar,

This is just something I put together so I could get some additional functionality from the CT website. As long as I have some free time, I am happy to help other people better their CT experience. No $ involved.

If you want to PM me a bit of info about your cellar layout, I will be happy to see if I can integrate it into the spreadsheet.

BTW this is developed on the latest version of Excel Office 365 but should work on any version from 2010 forward.



Andrew, I rarely check this Support section but am SO GLAD I DID to catch this thread! Judging by your tone, you seem willing to help others so my PM is on its way! (Feel free to ignore...if you must.)

_____________________________

The number of bottles I buy is nothing in comparison to the bottles I don’t buy. Let’s have a little perspective please.

(in reply to anevard)
Post #: 14
RE: Cellar Map - new spreadsheet - 1/10/2018 12:40:01 PM   
anevard

 

Posts: 109
Joined: 6/5/2006
Status: offline 		Glad people are getting some good use from this.

Latest changes:

I updated some of the code, cleaned up a few things and added an UnGraphedBins tab. When you select the UnGraphedBins tab, you will be asked if you want to refresh the ungraphed bins data. This may take a few minutes, depending on your cellar size. When completed, any bottles in bins that are not shown on this spreadsheet will be show here.



Happy to update the versions that are out there.

Also I just want to mention that as I tweak the code it is becoming easier for me to set up the layout for people. Some have asked if they can do it themselves. Honestly I would have to spend a lot more time explaining how to modify the formulas, formatting and code, than it takes me to set up a layout. Whenever I have a moment, I am happy to set one up. Don't hesitate to ask.


_____________________________

Andrew

(in reply to jmc167)
Post #: 15
RE: Cellar Map - new spreadsheet - 1/13/2018 9:51:02 AM   
jmc167

 

Posts: 81
Joined: 12/30/2015
From: Portland, Or
Status: offline 		Andrew -

Thanks so much, this latest addition of the ungraphed bins is excellent! Super appreciated, will be heading to the cellar to do more work today!

_____________________________

Just a guy trying to drink as much as I purchase, and losing this battle brilliantly!

(in reply to anevard)
Post #: 16
RE: Cellar Map - new spreadsheet - 1/16/2018 12:25:06 PM   
Andrew42

 

Posts: 177
Joined: 11/30/2015
From: Switzerland
Status: offline 		Andrew has been very kind and has spent time setting up my cellar. I cannot adequately express how grateful I am. I had an opportunity today to use it when someone gifted me two bottles. Using his spreadsheet to decide where to locate them was a breeze.

Thank you, Andrew, very much. I am extremely grateful. It is a great addition to CT.

(in reply to jmc167)
Post #: 17
RE: Cellar Map - new spreadsheet - 1/22/2018 3:24:58 PM   
anevard

 

Posts: 109
Joined: 6/5/2006
Status: offline 		Hi All -

Happy that those of you that are using this Spreadsheet (it needs a good name) are enjoying it. It gives me a lot of pleasure to know that in a small way, I am enhancing your wine experience.

I have been cleaning up and adding a lot of code and some interface enhancements. It's not quite there yet but hopefully by the end of this week. I have added a chunk of code to make it more efficient to set up the individual user layouts. Maybe one day I can automate this, but that's a long way off.

Also dealing with some Mac bugs (seems Excel 2016 is a bit less stable on Macs), which is hard since I am on a PC. I think I quashed them, so far.

A few big additions:

Location. Now each Bin Sheet can be a unique location. Bins can have the same name in different locations, which was not accounted for in the earlier versions. This was a lot harder than I thought, but I believe I have worked it out. I am still trying to optimize the unGraphedBins code as it runs a bit slower with the additional loops to check for Location. Also, some people use the Location field rather than the Bin field for their storage cataloguing. I have tried to keep the formulas and code as open ended as I can, but I am not yet sure I have figured out how to handle this yet. I will eventually.

Drink Dates. You can now visually display if a bottle is in the drinking window, ie. past the start drink date Or if a bottle is overdue to be drunk, ie. past the end drink date. This is fun, but boy was it tough to come up with formulas that didn't take 5 minutes to calculate. For those that really know Excel, UDFs were not the way to go!

See the new feature pics below. The 9999 and 0 are from the CT database drinking window fields. Not sure how to clean this up, but it will probably have to be done by each user on their individual wine drinking windows in CT. For multiple bottle bins, I am using the average of all the bottles. Coming up with a way to use the earliest or latest date, just slowed things down to a crawl.

If you are interested in enhancing your CT experience with my spreadsheet (name suggestions?), just drop me a PM. A bit busier now with work as well as the upgrade, but I'll get to everyone as soon as I can.







_____________________________

Andrew

(in reply to Andrew42)
Post #: 18
RE: Cellar Map - new spreadsheet - 1/26/2018 11:43:07 AM   
cigar52

 

Posts: 345
Joined: 8/15/2013
From: California... now Sarasota, FL
Status: offline 		Andrew,

Just wanted to followup and say how happy I am with the Bin-Select spreadsheet.

I have stopped using my paper graph ... which was a great time saver as I was moving a large number of bottles home from storage.

thanks again,

Murray

(in reply to anevard)
Post #: 19
RE: Cellar Map - new spreadsheet - 1/27/2018 9:21:41 AM   
anevard

 

Posts: 109
Joined: 6/5/2006
Status: offline 		My pleasure Murray.

Glad this is making your wine experience better. I am just trying to figure out a way to invert the Bin and Location fields in my formulas (without re-coding everything) so I can get your specific version onto the latest upgrade. Stay tuned.

Cheers,


_____________________________

Andrew

(in reply to cigar52)
Post #: 20
RE: Cellar Map - new spreadsheet - 3/4/2018 2:08:31 PM   
anevard

 

Posts: 109
Joined: 6/5/2006
Status: offline 		Some major code updates, settings and interface clean-up.



Also location bottle/bin space stats.



Just PM me if you are interested in having one set up for you.

Cheers,

_____________________________

Andrew

(in reply to anevard)
Post #: 21
RE: Cellar Map - new spreadsheet - 3/11/2018 4:23:42 AM   
Andrew42

 

Posts: 177
Joined: 11/30/2015
From: Switzerland
Status: offline 		Have to say that Andrew has been hugely reactive and helpful with a neophyte like myself. His spreadsheet adds greatly to the whole CT experience.

How about Bin Tracker for name?

(in reply to anevard)
Post #: 22
RE: Cellar Map - new spreadsheet - 3/12/2018 10:55:52 AM   
gmkwine

 

Posts: 2
Joined: 12/6/2014
Status: offline 		Andrew,
Your spreadsheet looks AWESOME!!
I would be grateful if you shared this.
Would you kindly post a URL or the file ?
Thank you
Gary

(in reply to anevard)
Post #: 23
RE: Cellar Map - new spreadsheet - 4/7/2018 1:30:50 PM   
anevard

 

Posts: 109
Joined: 6/5/2006
Status: offline 		Hi All,

Just an update. Microsoft has managed to regress some functionality in Excel somewhere between Excel 16 version 1802 (Build 9029.2253) and version 1803 (Build 9126.2116) whereby the query update fails. This occurs on PCs with these latest versions, I have no idea about Macs.

For now the solution is:

1. Don't update Excel!
2. Let me know if you are having the problem and I will get you an update as soon as I can. It's a bit of a kludgy solution , but it works

Sorry for anyone experiencing this but our issues don't seem to be a priority at Microsoft

Working on a few additions to the functionality, including a more automated setup process at my end, so I can get new users and upgrades done faster.

Cheers!

_____________________________

Andrew

(in reply to anevard)
Post #: 24
RE: Cellar Map - new spreadsheet - 4/22/2018 12:49:56 PM   
anevard

 

Posts: 109
Joined: 6/5/2006
Status: offline 		Hi again,

Finally got a few things done. I have now automated 80% of the setup process, so I can setup your cellar layouts a lot faster now. Also optimized some more code, which hopefully didn't create any new bugs. Dealt with the Microsoft Excel regression query refresh problem. Of course, I was just pushed a new version of Excel, so hopefully that won't cause any new issues.

I worked out a much more efficient way to show Simple Bottle Lists on various tabs.



I also created a better looking Bottles in Bin info popup.



On a more interesting note, I created a Red Bins List tab. This will will show you a list of any wines that are in a bin, anywhere in your cellar, that has been flagged red. They will have been flagged either by being over the Max Bottle count or having been added to the Check Bins list.





In my experience it's better to be on the Red Bins tab than being a Star Trek Red Shirt, but I digress.

I will upgrade current users as fast as I can and will happily set up new users as time permits.

Suggestions, thoughts, comments and ideas are very welcome.

Cheers,


_____________________________

Andrew

(in reply to anevard)
Post #: 25
RE: Cellar Map - new spreadsheet - 4/27/2018 1:08:57 PM   
anevard

 

Posts: 109
Joined: 6/5/2006
Status: offline 		Well the good news is that there seems to be some new content channels coming online. The bad news is that it has messed up my column parsing for the spreadsheet.

I believe that I have found a workaround that should be robust even with future channel additions. Between that, the Microsoft Excel query update regression and trying to get some paying work done, things have gotten a bit backlogged.

I am working to get all the current users updated and bring the new guys online asap. Hopefully there won't be any more curve balls for a while.

Cheers,



_____________________________

Andrew

(in reply to anevard)
Post #: 26
RE: Cellar Map - new spreadsheet - 5/1/2018 5:32:46 PM   
anevard

 

Posts: 109
Joined: 6/5/2006
Status: offline 		Hey all,

Here's what I am playing with. Might be interesting to add some dashboard stuff to the Cellar Map. Any thoughts?



< Message edited by anevard -- 5/1/2018 5:40:40 PM >

_____________________________

Andrew

(in reply to anevard)
Post #: 27
RE: Cellar Map - new spreadsheet - 5/2/2018 10:09:25 AM   
anevard

 

Posts: 109
Joined: 6/5/2006
Status: offline 		A bit more interesting



_____________________________

Andrew

(in reply to anevard)
Post #: 28
RE: Cellar Map - new spreadsheet - 5/16/2018 12:03:46 PM   
anevard

 

Posts: 109
Joined: 6/5/2006
Status: offline 		Hi again,

Working on getting those in the queue finished.

New versions are now going out with the dashboard, which has quite a few goodies that I haven't had a chance to document yet.



Also as a result of the dashboard development, I have added a feature to the wine popups. If you double click on a bottle in the wine popup, your default web browser will open to that wine's page on CellarTracker. If you have enabled login on the web-browser, than you will be on YOUR page for that wine in CT.



More info on the dashboard as I document it.

Cheers,

Andrew


_____________________________

Andrew

(in reply to anevard)
Post #: 29
RE: Cellar Map - new spreadsheet - 5/17/2018 7:46:41 PM   
anevard

 

Posts: 109
Joined: 6/5/2006
Status: offline 		Just finished updating the manual for the new version with the experimental dashboard. I have attached images of the relavent instruction for those that are interested. Cheers.






_____________________________

Andrew

(in reply to anevard)
Post #: 30
Page:   [1] 2 3 4 5   next >   >>
All Forums >> [Cellar Talk] >> CellarTracker Support >> Cellar Map - new spreadsheet Page: [1] 2 3 4 5   next >   >>
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts


Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI

0.156