Excel/question on your 3d vlookup and hyperlink.xls
I found the help you have posted on your link at https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/files.
I am looking in particular at the first file listed for 3d vlookup and hyperlink. In your example, you find a sheet name where a person's name appears using your array formula =INDEX(MySheets, MATCH(1, COUNTIF(INDIRECT("'" & MySheets & "'!A1:A50"), A3),0))
I need to do almost the exact same thing, however I would have names appear in multiple sheets and need to return each of the sheet names where the name appears. Can you help me figure out how to do that? I would be most appreciative.
Thank you for your time!
ANSWER: That formula is completely unsuitable to do multiple lookups of data across multiple sheets.
From the limited info in your question I would expect a VBA program to be the only way to accompish that, probably a non-trivial one, too.
---------- FOLLOW-UP ----------
QUESTION: Thanks Jerry. I will upload the example file to the site you indicated. Basically, I will be using your same files, but adding names to multiple sheets.
I would create columns and use the sheet names as the headers of those columns. The search names in column A, the sheet names across B1:F1.
Then this formula in B2 copied down and across will "mark" each column (sheet) where the column A name can be found.
=IF(ISNUMBER(MATCH($A2, INDIRECT("'" & B$1 & "'!A:A"), 0)), "x", "")
TO go the next step and return the matching HOURS instead of just marking an "x", the formula in B2 would be
=SUMIF(INDIRECT("'" & B$1 & "'!A:A"), $A2, INDIRECT("'" & B$1 & "'!B:B"))
And lastly, to just search the sheets and SUM the hours from all the sheets, go back to your original "list" of sheets over in column G, then the B2 ARRAY formula would be:
=SUM(SUMIF(INDIRECT("'" & $G$1:$G$5 & "'!A:A"), $A2, INDIRECT("'" & $G$1:$G$5 & "'!B:B")))
...confirmed with CTRL-SHIFT-ENTER.