How to get the sheet name from a cell value

[Karintorparn]

Hi

I have 3 suppliers, who gives me a price list and discount list.
The 6 documents have been merged to 1 to make it easier for me.
Sheet has been named after there supplier... (in my example Suppler_1, Supplier_2 etc...)

By writing the Item number in my Calculation sheet I get all the information I need...
All except who the supplier is.

Is there any formula to get the Supplier (sheet name) as a cell value depending on another cell?

I write the item nummber in cell A10 ( my Calculation sheet) and want the Supplier name (sheet name) to be displayed in cell B7.

[Adtxc]

=IF(COUNTIF(Supplier_1!A:A,Calc!A10),"Supplier_1",IF(COUNTIF(Supplier_2!A:A,Calc!A10),"Supplier_2",IF(COUNTIF(Supplier_3!A:A,Calc!A10),"Supplier_3","Not Found")))

[Karintorparn]

Thank very much

Works perfect

[kvsrinivasamurthy]

Pl see file. In each supplier sheet file additional Column E with formula added.
In Calc Sheet
In B10

Please Login or Register  to view this content.

In C10 then copied across

Please Login or Register  to view this content.