Forum Discussion
Filters as Worksheet Title
mathetes OK. I think I understand. You would create a worksheet ( separate from the data one) that can be filtered for any of the column headers, whereas, I (clunkily) created a separate worksheet for each of my likely filters. But your solution is what I originally had in mind. You’re a genius!
Yes, as you suggest, I would prefer to have a data worksheet without all the formatting.
I’ll play with it tomorrow, but I can’t see your entire A5 formula. What I see ends with “None…
mathetes Sorry, I hadn't yet opened the workbook you attached. There, I can see the entire A5 formula (obviously).
I studied it tonight. Wow, I'm impressed at your knowledge and organized layout. Very "elegant", as they used to say in coding.
Actually, you've probably already pegged me as an Excel rookie, so it won't surprise you that my actual database does have conditional formatting with colors for Source and Type. but you're right, I should save that fanciness for the Dashboard.
I hate to ask it, but what if I want to be able to filter any combination of all the columns? Like have a drop-down menu in row 1 for every column. Because, while I attached a simplified version, my actual database has 8 additional columns of detail about each tea, and I sometimes filter for those, too.
BTW, I have separate columns for the Aisles, because I might have a certain tea in two locations.
- Yes to direct message. Just click on a user's username in the top line of any forum postings. You'll be taken to a profile page, and up on the top right there's a "Message" box.... See you there.
Actually, I should be in the business, considering my inventory. But, alas, I am a tea neophyte when compared to those on the tea Discord servers I visit. If your wife wants to talk tea, I'm all ears.
I can share some workbook details with which the entire forum doesn't need to be bothered. I see that there is a messaging section in my Microsoft profile. Does that mean we could direct message?
Oh, I'm in Southern California. We don't have Wegman's out here.
THAT is quite a spreadsheet. You're in the business I gather. I'm worried that if I show this to my wife, who drinks only tea, she might want samples of everything! I'll be looking at it further and will let you know if I come up with anything creative.
Where are you located, if I might ask? The grocery chain I was referring to is Wegman's, active on primarily on the east coast I believe. Privately owned, but regarded as high quality and well managed.
As you might imagine, the more criteria you want to filter by, the more complicated the situation becomes.
Exactly! I was thinking of a nightmare nested IF formula when I wrote it.
Actually, I filter most often by the Location so I can print it out and check inventory in that location. Since I would never need a "double filter" to do that, I tried the following (in the workbook you attached) and it worked:
=FILTER(Varieties,(Varieties[Aisle A]=A1)+(Varieties[Aisle B]=A1)+(Varieties[Aisle C]=A1),"None found")
Note that I deleted the spaces in the beginning of the Aisle headers. They were to get the header to show above the Filter icon in my database.
I filter by other columns less often, so I can leave Filters in the headers of the database for when I want to see teas by a specific Type or Source.
All that having been said, it would help a lot...if you could be more specific about those 8 additional columns
So you can see all the columns, I'm attaching my entire workbook. In it, you can see the three worksheets I created for the inventories by location. I haven't yet created a "dashboard" sheet. But again, for now, I will use it for filtering for Location and do other filters in the database.
a local grocery store has started showing that kind of info on their app
I'll check my supermarket's app. That would be super helpful.
Thanks!
I hate to ask it, but what if I want to be able to filter any combination of all the columns? Like have a drop-down menu in row 1 for every column. Because, while I attached a simplified version, my actual database has 8 additional columns of detail about each tea, and I sometimes filter for those, too.
As you might imagine, the more criteria you want to filter by, the more complicated the situation becomes. It is possible to use FILTER to filter the array that results from a FILTER. Just as it's possible to nest IF functions multiple levels deep, adding to the complexity with AND or OR--though doing that inevitably becomes unwieldy--it's possible to do the same with FILTER. In fact FILTER to a great extent is a more powerful extension of the basic idea contained in the IF function.
All that having been said, it would help a lot--you apparently have experience in coding, so you know this would be true in basic programming as well--if you could be more specific about those 8 additional columns and the specific scenarios (and their frequency/priority) that cause you to want to incorporate them into the filtering. Is it frequent enough, predictable enough, that you could write one or two additional formulae to handle 80% of the situations, or is it more of an ad hoc and relatively infrequent "nice to have"?
BTW, I have separate columns for the Aisles, because I might have a certain tea in two locations.
I thought that the way you set up those separate columns was brilliant. It gave a visual clue that would be helpful by itself. I probably would have done something like A3, C6, etc. to indicate aisle and section in that row (a local grocery store has started showing that kind of info on their app, and it's helped me more than once).
