I have a Workbook that contains a "Search Tool" Sheet that I would like to pull multiple results from multiple sheets. All Sheets are formatted exactly the same containing different data. The "Search Tool" sheet is the same format as the rest but all blank with a Cell (C2) that is used for the search. I would like to be able to search a keyword in C2 that pulls every cell that contains that word in Column C of every sheet and row them to the blank rows in Column C of the "Search Tool" tab.
Ex.) C2 has a search word for "network"
C4 through C999 would then row every cell that has the word network in it from the Workbook. So if Tab "Maps" had 4 cells and Tab "Contacts" had 5 cells that contained the word "network" in them. then cells C2 through C10 would populate with cells from the Maps and Contacts tabs.
I have a similar formulae I use on a different sheet where this search function works referencing data from 1 single source sheet. I am now trying to get this to work from multiple sheets so I would think the formulae would be similar.
ex:)
=IF(ISERROR(INDEX('CLLI Lookup'!$C$5:$C$1001,SMALL(IF(ISNUMBER(SEARCH('CLLI Lookup'!$H$2,'CLLI Lookup'!$H$5:$H$1011)),ROW('CLLI Lookup'!$H$5:$H$1011)-ROW('CLLI Lookup'!$H$5)+1),ROW(2:2)))),"",INDEX('CLLI Lookup'!$C$5:$C$1001,SMALL(IF(ISNUMBER(SEARCH('CLLI Lookup'!$H$2,'CLLI Lookup'!$H$5:$H$1011)),ROW('CLLI Lookup'!$H$5:$H$1011)-ROW('CLLI Lookup'!$H$5)+1),ROW('Table of Contents'!1:1))))
Bookmarks