Which worksheet contains the value with 3D formula

Question

I have a workbook which has a sheet that finds the lowest price for each item in other worksheets. The items are all in the same cell in each sheet, and the "composite" sheet has the formula:

=MIN('sheet1:sheet3'!B3)

I would like the cell next to it to list the name of the sheet that the value was pulled from.

Answer 1

This is a big, ugly hack and doesn't scale well, but this will work for the way you have it now.

=IF(MIN(Sheet1:Sheet3!B3)=Sheet1!B3,"sheet1",IF(MIN(Sheet1:Sheet3!B3)=Sheet2!B3,"sheet2","sheet3"))

Answer 2

If you have many worksheets, First define the Name: Sheets using the Name Manager as :

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Pick some cell, say F1 and enter:

=IF(ROWS($A$1:A1)<=COUNTA(Sheets),INDEX(Sheets,ROWS($A$1:A1)),"")

and in G1 enter:

=INDIRECT(F1 & "!B3")

Copy these down. This will create of a table of sheets and values.

Finally in D1 enter:

=MIN(G:G)

and in E1 enter:

=INDEX(F:F,MATCH(D1,G:G,0))