Definitely place data in tables first, and reference the tables instead of static ranges. Even better - use PQ.
@jtmh315 ай бұрын
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.
@LynnEllison4 ай бұрын
Thanks! That was really simple!
@sheetskunk4 ай бұрын
No problem!
@user-ee6qs1xg2p5 ай бұрын
I don't want to use all sheets but using the control button is not working
@sheetskunk5 ай бұрын
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.
@rhysw19516 ай бұрын
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.
@sheetskunk6 ай бұрын
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 👉 kzbin.info/www/bejne/bmq0coR9btCFaa8si=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!
@rhysw19513 ай бұрын
@@sheetskunk That formula did the trick. Now searching across 3 pages. Thank you so much.