Looking up values across sheets and return not only the value but also the sheet name

[Pelikan]

Hi

I wonder if you guys could help me with my issue. I am looking up the largest value across various sheets (1 to 99) with the following formula:

=LARGE('1:99'!$B$1:$B$50;$C3)

That correctly returns the largest value in range B1:B50 across sheets 1 to 99.

However now I want to know the sheet name of the value above in a seperate cell, let say in: A3.
I'm using excel 2010.

Any suggestions?

Thanks a lot!

[ragulduy]

Have a look here:
http://www.excelforum.com/excel-form...heet-name.html

Perhaps you can use the formula in post 3:
=INDEX($A$2:$A$13,MATCH(TRUE,COUNTIF(INDIRECT("'"&$A$2:$A$13&"'!C14"),B2)>0,0))

you probably don't need to do it with the sheet names listed in A2:A13 - you can probably replace this with row(A1:A99) to generate the 1:99 numbers.

I think if this method doesn't work for you, you will need a UDF.

[Pelikan]

I guess I need a UDF... what is this?

[ragulduy]

You can write a custom function (user-defined-function) with vba. For example, something like:

Please Login or Register  to view this content.

might work if you are working with unique values. Press Alt+F11 to open the vba editon, then select insert->module and paste the code in the new module.

Then on your worksheet, you can use it as you would another function, so:
=find_sheet_name(A1)
should return the sheet on which the value in A1 is stored.