RE: Cellar Map - new spreadsheet

CellarTracker Main Site

RE: 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 >> RE: Cellar Map - new spreadsheet

Page: << < prev 2 3 4 [5] 6 next > >>

Message

<< Older Topic Newer Topic >>

RE: Cellar Map - new spreadsheet - 3/5/2023 6:17:19 AM

Stefan

Posts: 19
Joined: 12/25/2021
Status: offline

I’m ready to look at the map part as well. So if you give me an intro I’ll start setting that up as well.

(in reply to anevard)

Post #: 121

RE: Cellar Map - new spreadsheet - 3/5/2023 6:58:47 AM

anevard

Posts: 109
Joined: 6/5/2006
Status: offline

Here is the really basic way to create the bin maps, first select Develop Bin Sheets on the login sheet.

Fill in the non calculated columns on the AllBins List tab. H, I and K are the calculated columns and will only calculate when you update, or exit the tab. Choose to update the bin info when asked.

Add bin sheets and they will auto populate. You can then add column - row title info if you want, resize and or merge cells.

Finally finalize the bin sheet and it will clean up the formatting.

There are a lot more details to this process. I will answer questions as we move forward.

< Message edited by anevard -- 3/5/2023 7:23:38 AM >

_____________________________

Andrew

(in reply to anevard)

Post #: 122

RE: Cellar Map - new spreadsheet - 3/5/2023 1:09:22 PM

Stefan

Posts: 19
Joined: 12/25/2021
Status: offline

Just wanted to say that I have started to populate my own map and it works well with the straight forward single bottle bins. Nice feature that you could shift the information in the map cells!

I tried to change the frame around cells to draw a map that looked a bit more like the structure of my shelf. E.g. draw a thick line around a 4x4 cell/bin. But I could not make any difference to the graphical appearance. I assume that is blocked due to the automatic colouring? If this is enabled it could help to draw the diamonds as well. I have an idea of how to solve diamonds and other kinds of non standard format shelves this way.

Here's how far I got to start with:

(in reply to anevard)

Post #: 123

RE: Cellar Map - new spreadsheet - 3/5/2023 1:11:13 PM

Stefan

Posts: 19
Joined: 12/25/2021
Status: offline

Yes, BTW - I do not (yet) have any bins with double depth. So I will not be able to try that out. But perhaps now is the time to expand my cellar and build new the new shelves I have been thinking about. I can always blame I have to test the cellar map project

(in reply to Stefan)

Post #: 124

RE: Cellar Map - new spreadsheet - 3/5/2023 2:10:51 PM

anevard

Posts: 109
Joined: 6/5/2006
Status: offline

Anything I can do to help you get permission to expand your cellar is part of my mission

It is starting to look good. Glad you understood it. I wasn't sure it would be that intuitive.

Unfortunately all the cell formatting is done through conditional formatting and the lines and fonts can't be changed.

Also if you want to get rid of all the green triangles, in Excel Options Formulas, uncheck Cells containing formulas that result in an error.

Also once you are done, don't forget to finalize the sheet, it does some cleanup. Maybe backup before in case you mess it up.

BTW :
CTRL-SHFT-H on most sheets brings up a help file. (It needs some updating)
CTRL-SHFT-F on the Dashboard will bring up a list of filters
CTRL-SHFT-Z zooms the country region pie graph (There's a bug with the new drink window slicer I need to fix)
CTRL-SHFT-V on the Dashboard will bring up an outdated vintage chart (Not mine).
SHFT right clicking on the chart draws lines to clarify year/region list of filters
DBL clicking zooms the chart

< Message edited by anevard -- 3/5/2023 2:14:04 PM >

_____________________________

Andrew

(in reply to Stefan)

Post #: 125

RE: Cellar Map - new spreadsheet - 3/6/2023 2:49:31 AM

Stefan

Posts: 19
Joined: 12/25/2021
Status: offline

Thanks,
Nice shortcuts/helpers.

This is what I came up with after playing around a bit with the layout. For me this is good enough to solve the diamond shape bins etc. I used blank rows/columns where I removed the formula and just filled with black to make the dividers. If you have a complex layout with different sizes of bins it will not be a 1:1 excel to reality but I think it works really well this way.

Once you click "Finalize sheet" - is there a way to undo that? If you e.g. in a later stage want to rebuild your shelves?

Anyhow, here's a look at how my map looks at the moment:

(in reply to anevard)

Post #: 126

RE: Cellar Map - new spreadsheet - 3/6/2023 6:51:26 AM

anevard

Posts: 109
Joined: 6/5/2006
Status: offline

That looks great!

For the moment there is no way to undo the finalize step. Also no simple way to copy the formatting over to a new file when I update. The easy part is to copy the allbins list to a new file, as long as you only copy the text columns. My goal is to eventually create a copy over step for updated files. This could also allow undo of the finalize step. Best bet for now is to keep a backup before the finalize, but this doesn't really help with moving to an updated file.

The most important takeaway for the moment is that it looks like you will need to expand your cellar soon, those bins look pretty full !

Cheers,

_____________________________

Andrew

(in reply to Stefan)

Post #: 127

RE: Cellar Map - new spreadsheet - 3/7/2023 1:47:42 PM

anevard

Posts: 109
Joined: 6/5/2006
Status: offline

So I have pretty much completed the transfer formatting and all bins list utility functions. It is now pretty easy to transfer a sheet's formatting to another sheet in a new workbook as well as transferring the allBins List. This allows upgrading or making changes in a new workbook much easier.

For the moment there is minimal error trapping, but it works.

Although some things are still a bit rough, I am pretty sure that this new cellar map spreadsheet is in good enough shape for now.

_____________________________

Andrew

(in reply to anevard)

Post #: 128

RE: Cellar Map - new spreadsheet - 3/18/2023 8:42:58 PM

jmc167

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

Thanks Andrew - I just sent a PM to get a blank copy of the spreadsheet so I can help test

_____________________________

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

(in reply to anevard)

Post #: 129

RE: Cellar Map - new spreadsheet - 3/19/2023 8:02:29 AM

anevard

Posts: 109
Joined: 6/5/2006
Status: offline

Email sent with the latest version.

_____________________________

Andrew

(in reply to jmc167)

Post #: 130

RE: Cellar Map - new spreadsheet - 4/1/2023 12:41:32 PM

anevard

Posts: 109
Joined: 6/5/2006
Status: offline

Been playing with this consumed dashboard as part of the CellarMap spreadsheet.

_____________________________

Andrew

(in reply to anevard)

Post #: 131

RE: Cellar Map - new spreadsheet - 4/22/2023 8:10:56 AM

anevard

Posts: 109
Joined: 6/5/2006
Status: offline

I hope people aren’t scared away from trying CellarMap because it seems daunting to setup. It really isn’t and the more feedback I get the more user friendly I can try and make it.

_____________________________

Andrew

(in reply to anevard)

Post #: 132

RE: Cellar Map - new spreadsheet - 5/2/2023 12:09:04 PM

nedberg

Posts: 1
Joined: 5/1/2023
Status: offline

Hi anevard
I'd gladly test your CellarMap. I am competent in Excel and use both Mac and Windows, so I'll be able to test on both platforms. I don't have a very complex cellar, though. Just two cabinets, one with single slots and one with open shelves.

Let me know if you want me to test

(in reply to anevard)

Post #: 133

RE: Cellar Map - new spreadsheet - 5/4/2023 3:30:14 PM

anevard

Posts: 109
Joined: 6/5/2006
Status: offline

Hey Nedberg

Send me your email via PM and I’ll send you the latest version this weekend.

It’s probably worth reading the last few posts to get you started.

Cheers,

_____________________________

Andrew

(in reply to nedberg)

Post #: 134

RE: Cellar Map - new spreadsheet - 6/2/2023 3:39:54 PM

anevard

Posts: 109
Joined: 6/5/2006
Status: offline

Here is a link to download the latest version for those that are interested. Easier this way. There is a bug in the copy bin from another sheet but this wont affect the functionality of the file. Let me know if it works and if you have any questions on getting going. I hope this is useful for everyone, I use it every day!

CellarMap 10.1.03 download

< Message edited by anevard -- 6/3/2023 6:07:47 AM >

_____________________________

Andrew

(in reply to anevard)

Post #: 135

RE: Cellar Map - new spreadsheet - 6/2/2023 4:08:03 PM

Eric

Posts: 17314
Joined: 10/10/2003
From: Seattle, WA
Status: offline

quote:

ORIGINAL: anevard

Here is a link to download the latest version for those that are interested. Easier this way. There is a bug in the copy bin from another sheet but this wont affect the functionality of the file. Let me know if it works and if you have any questions on getting going. I hope this is useful for everyone, I use it every day!

CellarMap 10.1.03 file download

Hmm, I don't think it is visible. I get a 404 when trying to download and don't see that repo on your Git.

_____________________________

Cheers!
-Eric LeVine

http://twitter.com/cellartracker
http://facebook.com/cellartracker

(in reply to anevard)

Post #: 136

RE: Cellar Map - new spreadsheet - 6/3/2023 6:14:09 AM

anevard

Posts: 109
Joined: 6/5/2006
Status: offline

Oops. Not a GitHub expert.

Should be working now.

As it is a macro enabled Excel sheet, windows might lock the file, so you will need to unlock it on windows properties.

< Message edited by anevard -- 6/3/2023 6:15:04 AM >

_____________________________

Andrew

(in reply to Eric)

Post #: 137

RE: Cellar Map - new spreadsheet - 6/7/2023 2:24:56 PM

anevard

Posts: 109
Joined: 6/5/2006
Status: offline

Looks like people are downloading the file. I am just curious if it is working for everyone and any other thoughts.

_____________________________

Andrew

(in reply to anevard)

Post #: 138

RE: Cellar Map - new spreadsheet - 8/6/2023 5:11:56 PM

jmc167

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

Andrew -

Apologies for falling off the face of the earth after you sent me the early March release.

I finally able to have some time to work on this and downloaded the most recent version, but am unable to get my bottles to load in the individual sheet.

I entered my login data and switched to the individual bottles sheet, Excel states it is connecting to datasource in the bottom right-hand corner and then the "Refreshed from CellarTracker" pop-up occurs, but the sheet never populates.

I did download the webquery file from Cellartracker to ensure it was not specific to Excel for Mac thing (as we have run into that before) and it worked fine and downloaded my inventory as expected.

For grins, I also opened my "vintage" cellarmap v9.4702.1C and that is downloading as expected as well, so any thoughts on how to resolve this would be great!

Thanks!

_____________________________

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

(in reply to anevard)

Post #: 139

RE: Cellar Map - new spreadsheet - 8/8/2023 6:30:42 AM

anevard

Posts: 109
Joined: 6/5/2006
Status: offline

Hey

Glad to see you are trying the new version even if it’s not working for you

Take a look at this post. I believe it is a security issue in excel and power query. This is a solution that seems to solve the problem. However I am not sure this new version will work on a Mac. Please let me know.

CellarMap Excel security issue

< Message edited by anevard -- 8/8/2023 6:31:55 AM >

_____________________________

Andrew

(in reply to jmc167)

Post #: 140

RE: Cellar Map - new spreadsheet - 8/8/2023 7:22:20 AM

Sean McGrath

Posts: 371
Joined: 11/3/2020
From: US in NL
Status: offline

quote:

ORIGINAL: anevard

Hey

Glad to see you are trying the new version even if it’s not working for you

Take a look at this post. I believe it is a security issue in excel and power query. This is a solution that seems to solve the problem. However I am not sure this new version will work on a Mac. Please let me know.

CellarMap Excel security issue

That worked (on a Windows PC). Thanks, Andrew.

< Message edited by Sean McGrath -- 8/8/2023 7:28:26 AM >

_____________________________

(in reply to anevard)

Post #: 141

RE: Cellar Map - new spreadsheet - 8/8/2023 4:00:13 PM

jmc167

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

quote:

Take a look at this post. I believe it is a security issue in excel and power query. This is a solution that seems to solve the problem. However I am not sure this new version will work on a Mac. Please let me know.

Andrew - thanks so much, I found the equivalent setting in Excel for Mac, but no Joy, also I am seeing some failed queries per the Window below. If you want to send me the password for the sheet, I can do some digging and try and resolve the query errors.

On a final note, I renewed my Parallel's subscription to run Windows on my Mac, and as expected, the CellarMap sheet is working using Excel on the Windows virtual machine! I will now spend building bins and learning that cool new dashboard!!

Thanks and let me know your thoughts, here is the screenshot of the failed query errors:

< Message edited by jmc167 -- 8/8/2023 4:02:12 PM >

_____________________________

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

(in reply to anevard)

Post #: 142

RE: Cellar Map - new spreadsheet - 8/8/2023 4:05:44 PM

jmc167

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

quote:

I am competent in Excel and use both Mac and Windows,

Hi Nedberg -

Did you ever get this working on a Mac?

Per my posts below I have the sheet working on Excel running on a Windows Virtual Machine on my Mac, but could never get bottle list to load using Excel on my Mac.

Thanks!

_____________________________

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

(in reply to nedberg)

Post #: 143

RE: Cellar Map - new spreadsheet - 8/8/2023 8:22:12 PM

jmc167

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

Fellow CT Users - I have been able to complete the set-up from the existing 10.1.03 file from Github rather quickly (using the Windows version of Excel), so if you need any help, please let me know and I will be happy to assist you, either via the forum, PM's or via a Zoom call to walk you through it via a screen share.

Hi Andrew - so this is just a fantastic App/Spreadsheet - thank you so much!!! It has come a long way since our emails and initial versions on Christmas day in 2017!! The only question I have at this time are:

1) So earlier this evening, per the screenshot below, I could not move my cursor past column R, so was unable to select any cells beyond column R and view the bottles in that bin, or do any formatting. I then moved the Bins to left (starting in column A) and then the cursor would not move past column O, so it seemed the cursor could never get to the last three columns in the sheet. After moving the Bins from Rows X through AJ to column A start at Row 30, now I can navigate to all cells, so perhaps I just needed to run the refresh bins a few times.

2) Can you remind me what the Check Bottles List sheet is for?

3) Also, am I remembering that the Red Bins List sheet, shows all bins with more bottles in them than configured (i.e. 4 bottles in a 2-bottle bin)?

Thanks in advance for any thoughts on this - below is the screenie showing the inability to Navigate past Column R.

< Message edited by jmc167 -- 8/8/2023 10:27:45 PM >

_____________________________

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

(in reply to jmc167)

Post #: 144

RE: Cellar Map - new spreadsheet - 8/10/2023 3:52:10 PM

anevard

Posts: 109
Joined: 6/5/2006
Status: offline

Really glad people are using this 😀

As for Macs, I am pretty sure this is a non starter. A lot of the VBA and some of the Power Query stuff is probably not supported on Macs. Sorry !

I ran into the scrolling bug as well but it was also very good at hiding and reappearing. Is it resolved fully for you?

On my end I don’t have the issue. I was working on a somewhat newer version that would allow simple migration from a previous version 10, but when no one seemed interested I stopped working on it. Part of that was trying to track this issue down.

The more discussion there is publicly, hopefully the more people might play with this. Then if and when I have time I can keep improving it. I use it every day, mostly for the dashboard.

Enjoy.

_____________________________

Andrew

(in reply to jmc167)

Post #: 145

RE: Cellar Map - new spreadsheet - 8/10/2023 11:15:34 PM

jmc167

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

quote:

Is it resolved fully for you?

Hi Andrew, understood about the Macs version of Excel, not a big deal since Parallels is working.

As to the scrolling - yes it is working now - hurray!

Once I changed the bin layout for where the bins located on the left wall of the locker were stacked "above" the bins on the right wall, all works well. For some reason having the bins for each wall next to each other on the page caused the scrolling to freeze at certain columns.

The only questions I have now are:

1) Per the screenshot below, when I click on any bin for the EuroCave the spreadsheet lists way more than the 12 bottles that are in the bin. I list some OWCs as bins and do not have this problem - any ideas?

2) Also per the screenshot below, the Pricing on the Dash report does not look like yours.

Other than these two things, I am having fun! Here are the screen shots:

[

_____________________________

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

(in reply to anevard)

Post #: 146

RE: Cellar Map - new spreadsheet - 8/12/2023 9:33:37 AM

anevard

Posts: 109
Joined: 6/5/2006
Status: offline

quote:

ORIGINAL: jmc167

The only questions I have now are:

1) Per the screenshot below, when I click on any bin for the EuroCave the spreadsheet lists way more than the 12 bottles that are in the bin. I list some OWCs as bins and do not have this problem - any ideas?

I am pretty sure I resolved this bug. Are you sure that the Location Bin Name combo are unique in the spreadsheet. Also are there any spaces in the bin names. I can't recreate this issue.

quote:

ORIGINAL: jmc167

2) Also per the screenshot below, the Pricing on the Dash report does not look like yours.

Other than these two things, I am having fun! Here are the screen shots:

No idea on this. I know that there is a bug with currencies that use commas rather than points for the decimal separator. I am unable to resolve that at this time.

As for the Red Bin List, yes that indicates bins that have more bottles than allowed.

The check bins list is a list you can add botlles to by dbl clicking their barcodes. These can then be checked on CellarTracker or for any other purpose. They are deleted from the list by right clicking. Sometimes I use this to make a list of wines for a specific occasion.

_____________________________

Andrew

(in reply to jmc167)

Post #: 147

RE: Cellar Map - new spreadsheet - 8/12/2023 2:45:57 PM

jmc167

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

quote:

ORIGINAL: anevard
I am pretty sure I resolved this bug. Are you sure that the Location Bin Name combo are unique in the spreadsheet. Also are there any spaces in the bin names. I can't recreate this issue.

No idea on this. I know that there is a bug with currencies that use commas rather than points for the decimal separator. I am unable to resolve that at this time.

As for the Red Bin List, yes that indicates bins that have more bottles than allowed.

The check bins list is a list you can add bottles to by dbl clicking their barcodes. These can then be checked on CellarTracker or for any other purpose. They are deleted from the list by right clicking. Sometimes I use this to make a list of wines for a specific occasion.

Andrew -

Thanks for the updates, here are my responses:

1) I checked and yes, all the Location Bin Name Combos were unique. In looking at this further, however, I noticed that all my other locations that are working as expected had a minimum of a three-character bin name, whereas my Eurocave only had a single-character bin name, so I changed this to a two-character bin name and it now works as expected.

2) I will fiddle with the bottle list to see if I can get the dash report by price graph working as expected.

3) Thanks for the tip on the check bin list, that will be just the ticket for listing the wines to pull from storage to restock my Eurocave at home.

Thanks again for the amazing tool!

_____________________________

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

(in reply to anevard)

Post #: 148

RE: Cellar Map - new spreadsheet - 10/31/2023 11:50:21 AM

pinskycellar

Posts: 6
Joined: 2/26/2019
Status: offline

Hi Andrew -

So, I've got the Front and Back bin maps built for my cellar and they seem to be populating correctly. What I can't get working is the Depth Chart map. Maybe I don't understand what is required to make that work. Even if I put values in the DepthCell column, nothing is ever shown in that cell. For example, for bins A1-F and A1-B, I put B55 for the DepthCell for both but nothing is displayed in cell B55.

Some other observations:

- If you have multiple tabs for different locations and those locations share a common bin name (Cellar1-A1 and Cellar2-A1), when you double click on that bin in either tab, all bottles from both tabs are listed. Similarly, if a bin shares the same name as the location (House-House), then when you double click on that bin, all bottles are listed twice.

- It seems that if you don't have enough bins across a tab, you can't access the dropdown that allows you to see different views (MaxBottles, BottlesofMax, etc) on that tab.

- You can't match bottles based on only location with no bin specified. This would be useful for locations where I don't track bins like inside my house. As a workaround, I created a fake bin name called "Inside" for a couple of locations which lead to the discovery above.

All in all though, I'm really digging the self-service aspect of this new generation of the spreadsheet. If I can get the Depth Chart working, I think I can work around the other little idiosyncrasies.

Thanks!

(in reply to anevard)

Post #: 149

RE: Cellar Map - new spreadsheet - 11/4/2023 12:37:57 PM

anevard

Posts: 109
Joined: 6/5/2006
Status: offline

Sorry it takes a while to provide answers, but I have very little free time these days. I do really like that people are using this so I will try and help whenever I can. See my answers below.

quote:

ORIGINAL: pinskycellar

Hi Andrew -

So, I've got the Front and Back bin maps built for my cellar and they seem to be populating correctly. What I can't get working is the Depth Chart map. Maybe I don't understand what is required to make that work. Even if I put values in the DepthCell column, nothing is ever shown in that cell. For example, for bins A1-F and A1-B, I put B55 for the DepthCell for both but nothing is displayed in cell B55.

So you actually need 3 line items in order to display a depth chart bin. You need the front and back items as well as the depth chart item. See the image below with the 3 items for a particluar bin highlighted. It was the simplist way I could make things work, although it does require the user to enter that 3rd line. I was hoping to automate that someday, but I just don't have enough bandwidth.

quote:

ORIGINAL: pinskycellar
Some other observations:

- If you have multiple tabs for different locations and those locations share a common bin name (Cellar1-A1 and Cellar2-A1), when you double click on that bin in either tab, all bottles from both tabs are listed. Similarly, if a bin shares the same name as the location (House-House), then when you double click on that bin, all bottles are listed twice.

Hmm, that seems like a bug. Not a situation in my cellar so I will have to try and hunt it down when I have some free time.

quote:

ORIGINAL: pinskycellar
- It seems that if you don't have enough bins across a tab, you can't access the dropdown that allows you to see different views (MaxBottles, BottlesofMax, etc) on that tab.

Well that dropdown does extend across 8 columns, so I guess if your bins don't go that far across it is an issue. There is some code that prevents scrolling too far to the right of the bins once the sheet is finalized. I could eventually prevent that scrolling from being no less than 8 columns in a future version. In the meantime why not put your bins a little further to the right so that they end at the 8 or 9th column even if there is nothing to their left.

quote:

ORIGINAL: pinskycellar
- You can't match bottles based on only location with no bin specified. This would be useful for locations where I don't track bins like inside my house. As a workaround, I created a fake bin name called "Inside" for a couple of locations which lead to the discovery above.

All the code revolves around Location-Bin combinations, except when there is a bug like the one mentioned above. So you do need to have some sort of bin name.

quote:

ORIGINAL: pinskycellar
All in all though, I'm really digging the self-service aspect of this new generation of the spreadsheet. If I can get the Depth Chart working, I think I can work around the other little idiosyncrasies.

Thanks!

Glad it is pretty much working for you. Makes me happy to help people drink more wine, except that prices are getting insane.

_____________________________

Andrew

(in reply to pinskycellar)

Post #: 150