I feel like this should be fairly simple, but I just can't find the right combo of functions to do both. Finding the max or min value between sheets is fairly straight forward, but how can you return the min/max value AND the sheet name which it came from?
=MAX(Sheet1!A2,Sheet2!A2,Sheet3!A2,Sheet4!A2) gives me the highest value for 'A2' between all four sheets; can this function be combined with an index/match or IF function to return which sheet the max value was found on? The max value and sheet name could be in two different cells; just trying to return them both on the summary sheet.
=MAX(Sheet1!A2,Sheet2!A2,Sheet3!A2,Sheet4!A2) gives me the highest value for 'A2' between all four sheets; can this function be combined with an index/match or IF function to return which sheet the max value was found on? The max value and sheet name could be in two different cells; just trying to return them both on the summary sheet.