Formula To Create A Dynamic Title While Using Filters

Hello,

I have a table, with filters. When I filter to something, I would like to have another cell automatically update with the name of what has been filtered. I have included an example of what I am trying to create. Hope this makes sense. Thanks.

Hi,

Try this array (important) formula:

=INDEX($B$4:$B$26,SMALL(IF(SUBTOTAL(3,OFFSET($B$4,ROW($B$4:$B$26)-MIN(ROW($B$4:$B$26)),,,))>0,ROW($B$4:$B$26)-MIN(ROW($B$4:$B$26))+1),1))

Regards

Originally Posted by XOR LX

Hi,

Try this array (important) formula:

=INDEX($B$4:$B$26,SMALL(IF(SUBTOTAL(3,OFFSET($B$4,ROW($B$4:$B$26)-MIN(ROW($B$4:$B$26)),,,))>0,ROW($B$4:$B$26)-MIN(ROW($B$4:$B$26))+1),1))

Regards

When I put that formula into cell H3 of the initial file I included, it doesn't seem to work when i filter to another type of fruit. If I don't filter to anything the formula brings back "Apples".

And I trust you didn't miss the underlined, emboldened part about entering this as an array formula?

Worked fine for me.

Regards

Oh you're right. I'm sorry I forgot the special way to enter the formula. It works perfectly now! Thanks!

You're welcome.

I'm sorry, there is one more thing I forgot to look for. Is there a way to add to the formula that if nothing is being filtered cell H3 will say "Combined"?

Actually, that first part can be a lot simpler:

=IF(SUBTOTAL(3,$B$4:$B$26)=ROWS($B$4:$B$26),"Combined",INDEX($B$4:$B$26,SMALL(IF(SUBTOTAL(3,OFFSET($B$4,ROW($B$4:$B$26)-MIN(ROW($B$4:$B$26)),,,))>0,ROW($B$4:$B$26)-MIN(ROW($B$4:$B$26))+1),1)))

Regards

That fixes it! Thanks again!

Sorry, you did mention that in your attachment. Again, array-entered:

=IF(SUM(SUBTOTAL(3,OFFSET($B$4,ROW($B$4:$B$26)-MIN(ROW($B$4:$B$26)),,,)))=ROWS($B$4:$B$26),"Combined",INDEX($B$4:$B$26,SMALL(IF(SUBTOTAL(3,OFFSET($B$4,ROW($B$4:$B$26)-MIN(ROW($B$4:$B$26)),,,))>0,ROW($B$4:$B$26)-MIN(ROW($B$4:$B$26))+1),1)))

Regards

You're welcome again!