Question

How should I get Google Sheets to help manage an interactive inventory sheet?

I've tried using pivot tables from a master data list we update:
master-data.PNG

We have over 50 beer taps in 3 different locations. I originally used other pivot tables to pull data to an area I wanted the updated beer to go, but when the first pivot table changes, so does everything else. I wanted a setup kind of like this:

sheet2.PNG

If a pivot table could yank data from the master list based off an overall receiving column, then if I used an index match function to sort where I want beers to go, that would be amazing. I had tap numbers 1 - 30 that I did a drop down list from the on hand list to have the beer go to which tap handle and replace the beer. If I could have the beer sort like that and then add how many kegs of the beer went to that location, then maybe once I moved kegs from original pivot table it would refresh but keep all data located in the other areas.

I want to just sort beer as it is recieved each week to the areas. 50 beers is hard to manage and to be able to do this per price point would be amazing. I wish pivot tables would stay static or something when snagging data because then it would be amazing.

Do you have any suggestions on how to improve this?

Mentioned
#Google Sheets #Spreadsheets
Share
bigal123's avatar
a year ago

I think you might need a macro in Google Sheets to do the sorting of the beer as you mentioned. Curious how the "Receiving" column will look on the master list and how you envision the workflow of sorting to work.

In terms of the Pivot Tables staying "static," is it that the filters on the PivotTables aren't enough to keep the Pivot Tables from changing?

2 points
VictorLai's avatar
a year ago

The QUERY() function might do what you need.

1 point
How do you manage your chat inbox?

Hey guys, first post here. As part of my work, I have to deal with and respond to a lot of incoming messages from different chats: Linkedin/WhatsApp/Signal/IG. I try to use Unreads/Archive features...

What are your favorite small, single-purpose apps?

There are plenty of advanced, powerful software filled with enough features to do almost anything. And then there are the tiny utilities that don't do much, but that are great at what they do do. ...

Do you use Readwise app? How much are you paying for the service?

I came across Readwise this week and I thought it was a very nice app. It sends emails with highlights from Kindle books, Instapaper, Pocket, iBooks and other services. It also offers the spaced re...

The community for Google Sheets  power users.