Return Cell Contents by Sheet Name Search

Hello all.

I've run into another slight holdup with my restaurant work. I am setting up a sheet to use for menu costing and I need the contents of a cell to be copied in based on a name match from SHEET names.

I know how to do this with VLookup when the names are all gathered in one worksheet, but I don't know how to use this when the names are sheet names within a workbook.

To clarify..

On sheet 1 I enter Apple Sauce into A4. In that same workbook there is a sheet named Apple Sauce (along with maybe a hundred other randomly named sheets.) I want the content of, say, B1 from the Apple Sauce sheet to automatically plug in to sheet 1 B4 when I type Apple Sauce in A4 of sheet 1.

I've used this equation.. =VLOOKUP(A7,[InventoryMain.xlsx]Sheet1!$A$1:$H$1000,8,FALSE).. to accomplish this via a search of a single table on a single sheet. I just don't know how to make it search via the names of sheets in one workbook rather than via a table.

Thanks for any help!

That's perfect. Thank you!!!

How would I do this to do the exact same thing except look through the sheets on a seperate workbook? Say the workbook name is Test. I can't figure out where to enter it into the formula to make it work.