I have a sheet for each data set i have. I want to display in a cell the name of the sheet with the highest value in all the B2 cells.

  • pls share a copy of your sheet
    – user0
    Apr 14 '19 at 20:28

Roughly stated, the below formula will find "the highest B2", count how many "B2s" it had to look in to then find that value, then count that same number into a list of sheet titles to print the right one. You'll have to fix the formula to hold your actual dataset sheet names, but at least the pattern is straightforward. That's also why it is broken up onto multiple lines—for ease of editing. Do keep an eye on those double-quotes, single-quotes, and …!B2 references if you try this approach.

There are other ways to accomplish your goal, but I'd guess none so accessible and maintainable.

"Dataset 1","Dataset 2","Dataset 3"
'Dataset 1'!B2,'Dataset 2'!B2,'Dataset 3'!B2
'Dataset 1'!B2,'Dataset 2'!B2,'Dataset 3'!B2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.