Search all tabs in a workbook using a formula

[maximillianrg]

Hello Excel Masters

• I have a workbook with 35 tabs in it and I have added a 36th tab called "Max"
• In Column A of tab Max I have a list of all other tab names
• In cell B1 of tab Max I have the word "foo"
• In cell C1 of tab Max I need the magic formula

The magic formula searches all 35 workbooks for the contents of cell B1 ("foo") and returns 1 or "True" if it finds it and 0 or "False" if it does not. I tried using the below formula but it does not find all matches, for example of the call contains "afoo" it would not work

=VLOOKUP(B2,INDIRECT("'"&INDEX(MySheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&MySheets&"'!$A$1:$AC$50000"),B2)>0,0))&"'!A:AC"),1,0)