Filters as Worksheet Title

[scrail2004]

I have an Excel worksheet that is an inventory list 17 columns wide.  I have Filtered the column headers (row eight) and I filter by various columns to generate different lists (I tried it with slicers, but they were more clunky for me to use).

I would like the header of the worksheet to change based on the filters applied.

I picture showing the filters in cells A1, A2, and A3, or perhaps in text boxes across a fat row 1.

Does anyone have a solution?

[#2]

Did you intend to add images or (far better) to attach a copy of the workbook? That would help us help you a lot more than your verbal description.

[#3]

Sorry, this is my first post. I couldn't find how to add an attachment. How do I do that?

[#4]

@scrail2004 

After you start to reply, click on "Open full text editor"...down toward the bottom of the section that is then presented you see a place where you can drag and drop a file. If that doesn't work for you, use OneDrive or GoogleDrive and paste a link here.

[#5]

@mathetes Oh, full text editor is pretty slick.  But there is no window into which I can drop a file.  And I've never used OneDrive or GoogleDrive before.  The best I could figure out is to make the worksheet a jpeg file and paste it here (super-simplified version).  It's an inventory of teas.

So if I filter the column headers and filter Type to show only Black teas, I'd like the title to read "Black".  Or if I filter Source to show only Mei Leaf teas, I'd like the title to read "Mei Leaf".  Or if I filter both of those, I'd like the title to read "Mei Leaf Black".

[#6]

@scrail2004 

A jpeg which is small and low res, at least to my octogenarian eyes.

Anyway, how are you filtering? Might I suggest that you use the FILTER function rather that the tool bar based filter. That way you could enter the word "Black" in a cell, a cell that is formatted to serve as a header, and use that same cell as the criterion in your FILTER function. The formula would look something like this, assuming the criterion "Black" is in cell A1; and column E is where the Type appears.

=FILTER(database,E15:E100=A1)

To make it more complicated and add the "source" as a second criterion, with"Mei Leaf" entered into cell B1

=FILTER(database,(E15:E100=A1)*(F15:F100=B1))

I don't know why you aren't seeing an area to drag and drop your file. I've got the full edit window open now, and I'm getting a screen grab of this sentence and what's below it

[#7]

@mathetes Thanks for the quick reply.  Yes, I know the jpeg was not ideal.  My eyes are sexagenarian, so I get it!

When I first came up with this issue, I did exactly what you suggest.  I created worksheets (tabs) for all the ways that I typically filter my data and using the Filter formula, created all the lists I need.  I just hide the columns that are in my data worksheet, but I didn't want in the "printouts".  And it was easy, obviously, to put what ever header I wanted on each worksheet.

I was just hoping there was a way to avoid adding all those worksheets.

BTW, I can now see the drag and drop box!  I'll drop the file in, just to test it.

[#8]

@scrail2004 

When I first came up with this issue, I did exactly what you suggest.  I created worksheets (tabs) for all the ways that I typically filter ...... <snip> ......I was just hoping there was a way to avoid adding all those worksheets.

I'm not sure what I said to make you think I was suggesting a lot of worksheets. At most I would envision three.

• One of those would be just a convention I follow, which is putting Data Validation lists (or other "background tables") on their own page so as to be out of the way of the working sheets.
• I would also separate the database, putting it on it's own sheet,
• and then have what's commonly called a "dashboard" sheet, which would be where you'd do whatever fancy formatting you want for output.
  • I say that because in general it's not a good idea to play with fancy formatting on the "input" or "raw data" end of things. Fancy formatting includes doing such things as merging cells, certainly adding color (which you've not done but many do)

Anyway, I have constructed a formula here that will accept your choice of Type and, optionally, choice of Source, and produce a nice output list. At present dashboard I created is showing everything. That can easily be modified in the single formula that does it all. This is the formula, and it appears in cell A5 of the dashboard.

=IF(ISBLANK(E1),
FILTER(Varieties,Varieties[Type]=D1),
FILTER(Varieties,(Varieties[Type]=D1)*(Varieties[Source]=E1),"None found"))

Here's what it looks like with "Black" selected

[#9]

@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…

[#10]

@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.

[#11]

@scrail2004 

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).

[#12]

@mathetes 

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!

[#13]

@scrail2004 

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.

[#14]

@mathetes 

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.

[#15]

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.