Formula help on VlookUp using worksheet name

[D_Spark]

I have the following formula on a worksheet called "Master"

If works find but I need to exclude the worksheet name of "Master"

What Im experiencing is that the Value of B2 may appear on worksheets "Master" and another worksheet

The formula as is returns "Master", but I want it to exclude referencing "Master" and then return the value shown in B1 from any other worksheet in the workbook.


=SUBSTITUTE(CELL("filename",B2),LEFT(CELL("filename",B2),FIND("]",CELL("filename",B2),1)),"")

 

[oldbrewer]

how many sheets do you have and is the value you are looking for only on one sheet ?

 

[D_Spark]

I have 15 sheets including the "Master" one.....and yes the value that Im looking for will only appear on one other sheet to the "Master" sheet