Excel Filter Function Across Multiple Sheets

Подписаться 1,1 тыс.

Просмотров 6 тыс.

50% 1

FILTER in Excel has sooo many uses, sometimes it feels endless. And one such use, is to filter on data spread across multiple sheets.
With the help of the VSTACK function, you can combine the columns from your entire worksheet and make a multi-functional search tool for your data. This is great for rows of historical statistics related to purchasing, member services, and more.
Just remember, when setting this up, you want to make sure your sheets all share the same columns!
00:00 Do more with the FILTER function
00:27 Setting expectations
01:34 Using VSTACK for multiple sheets
03:02 FILTER out the empty rows
03:54 FILTER on certain values
05:20 Handling no matching values
06:40 Expanding on FILTER
Looking for more Excel help? ➡️ sheetskunk.com
Let's connect on social! 👨‍💻📲
TikTok ➡️ / sheetskunk
Instagram ➡️ / sheetskunk
Pinterest ➡️ / sheetskunk

Опубликовано:

30 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 12

@nadermounir8228 6 месяцев назад

Thank you for this insightful video 📹

@alexrosen8762 5 месяцев назад

Wow... great stuff 👌

@LynnEllison 3 месяца назад

Thanks! That was really simple!

@sheetskunk 3 месяца назад

No problem!

@MaydayAggro 5 месяцев назад

Definitely place data in tables first, and reference the tables instead of static ranges. Even better - use PQ.

@jtmh31 5 месяцев назад

Preach. I tell people that if you're not using tables to manage your data, you're doing it wrong. Structured references are so much better to use in formulas.

@rhysw1951 5 месяцев назад

Hello and thanks for this trick. A question though, is it possible to have the search function over 3 columns not just one.? If i expand the search to another column I get a value error. At the moment my work around is to a sheet for each type of search. Not very ideal. i also want to incorporate that results start populating as I type but I use excel rarely so its slow going haha.

@sheetskunk 5 месяцев назад

Hi! Yes, you can do this. I have another video that somewhat breaks this down with one sheet, but you can adjust the formula to include multiple. The video also tells you how to make it filter down and change the results as you type. Here's the video 👉 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-89QAQG9kR3M.htmlsi=mJa7u6U8LeGJAl7R But as a quick example, to filter on multiple sets of data, your formula would look something like this: =FILTER(VSTACK(Table1,Table2),ISNUMBER(SEARCH(Sheet2!$B$1,CONCATENATE(INDEX(VSTACK(Table1,Table2),0,1),INDEX(VSTACK(Table1,Table2),0,2),INDEX(VSTACK(Table1,Table2),0,3))))) Although in this formula, I'm only combining 2 sheets (or tables), and specifying the first 3 columns to search on. And cell B1 is where I would enter the text to search on. Hope that helps!

@rhysw1951 2 месяца назад

@@sheetskunk That formula did the trick. Now searching across 3 pages. Thank you so much.

@sheetskunk 2 месяца назад

That’s great! Glad it worked out 💪

@user-ee6qs1xg2p 4 месяца назад

I don't want to use all sheets but using the control button is not working

@sheetskunk 4 месяца назад

Hmm, that's strange. Well, instead of shift or control, you can select the range from a single tab, then enter a comma. After the comma, select the range from another tab. For more tabs, just repeat the process.