Look Up Sheet Name in Workbook Based on Cell Value

[JLeibovitz]

What I'm looking to do:
I'm hoping someone knows how to use either a formula or VBA to lookup a value in A2:A200 and populate column J2:J200 with the Sheet Name that value is found on. I've built a workbook that assembles a list of items across multiple sheets using the following name definitions and formulas:

NAME MANAGER:
Arry1: =MMULT(0+(ROW(INDIRECT("1:"&COUNTA(Sheets)))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(Sheets))))),TRANSPOSE(COUNTIF(INDIRECT("'"&Sheets&"'!B2:B200"),"SL1")))
Sheets: ={"Product","Games as a Service","Marketing"}

Formulas on "SL1 Worksheet" (List page):
To define how many items across the workbook that fit my criteria and inform the list building =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!B2:B200"),"SL1"))
The array formula being used in A2 to build the list (copy-pasted across A2:H200) =IF(ROWS($1:1)>$J$1,"",INDEX(INDIRECT("'"&INDEX(Sheets,MATCH(TRUE,Arry1>=ROWS($1:1),0))&"'!A2:i200"),SMALL(IF(INDIRECT("'"&INDEX(Sheets,MATCH(TRUE,Arry1>=ROWS($1:1),0))&"'!B2:B200")="SL1",ROW(INDIRECT("1:"&ROWS($A$3:$I$200)))),IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+Arry1),ROWS($1:1))),COLUMNS($A:A)))


Any ideas? I know that putting =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) in a cell on the worksheet results in the name of that worksheet being printed in the cell, but I've got zero clue how to put that into any good use here.

 

[JLeibovitz]

Dug around yesterday after posting and found this but I can't for the life of me figure out how to apply it - nevermind get it to work. Thoughts?
How to lookup a value across multiple sheets and return sheet names [SOLVED] | Chandoo.org Excel Forums - Become Awesome in Excel