indirect nested in vlookup to reference a variable sheet name in a separate workbook

I am a chef. Using excel-mac 2011.

The 3 work books I have are:
MASTER INVENTORY ... all purchased items with relevant data. Ending with the price per OZ. of all ingredients.

MASTER RECIPE. All recipes for the establishment. I use vlookup to get the price per oz from the master inventory book.. works great. Also have nested indirect to look up any sub-recipes used.
*****IF(D6=0,0,IFERROR(VLOOKUP(A6,'Mac:Users:chef:Downloads:[master inventory.xlsm]Sheet1'!$B$3:$I$673,8,FALSE),IFERROR(VLOOKUP("PRICE PER OZ.",(INDIRECT("'"&$D6&"'!D35:H41")),4,FALSE),"ingredient issue")))***** D6 is the name of another recipe in the book. IE: roasted garlic in caesar dressing

PLATE COST. same vlookup to find the raw ingredient cost from the master inventory however can not get the nested indirect to work because it is in a separate workbook that is open. Trying to make it find the recipe name that is a sheet in the recipe book. I have flipped a few things around to test it out but can't seem to get it right... think I need to make a separate sheet in the master inventory workbook that is a summary of the recipes... but am trying to avoid extra data input.

example:

Caesar Salad plate cost:

Caesar dressing--house made item. can't get the price per oz from the recipe. The recipe is a worksheet in the master recipe book.
romain lettuce--purchased item. VLookup works fine
coutons--purchased item. VLookup works fine

JeteMc,

Thanks for the welcome and the suggestion. I will look at the suggested workaround as well as upload a couple sample workbooks with the caesar salad example tomorrow.

see uploads

Try

=IF(B8=0,0,IFERROR(VLOOKUP(A8,'[master inventory.xlsm]Sheet1'!$B$3:$I$646,8,FALSE),IFERROR(VLOOKUP("PRICE PER OZ.",INDIRECT("'[master recipes.xlsm]" & B8 &"'!$D$35:$G$41"),4,FALSE),"ingredient issue")))

works great! thanks.. great solution!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

....beaten to the punch!