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.
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.
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 👉 ua-cam.com/video/89QAQG9kR3M/v-deo.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!
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.
Awesome! exactly what I needed and very easy to follow!
Wow... great stuff 👌
Thank you for this insightful video 📹
Thanks! That was really simple!
No problem!
Definitely place data in tables first, and reference the tables instead of static ranges. Even better - use PQ.
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.
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.
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 👉 ua-cam.com/video/89QAQG9kR3M/v-deo.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!
@@sheetskunk That formula did the trick. Now searching across 3 pages. Thank you so much.
That’s great! Glad it worked out 💪
I don't want to use all sheets but using the control button is not working
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.