Finding the highest value across worksheets and returning the worksheet name

**Darlo** · 04-30-2007, 11:58 AM

Hi guys!

I have a workbook containing 13 worksheets - 12 worksheets containing the monthly sales figures which are named January through to December and 1 worksheet to summarise the annual figures.

I am trying to find the highest value in cell C14 across all 12 monthly worksheets and then display the worksheet name as the result....is this possible?

I am also trying to do exactly the same as the above but display the actual figure of the highest amount rather than the worksheet name.

Any advice on these two issues would be very much appreciated.

Thanks in advance.

Darlo

**oldchippy** · 04-30-2007, 05:20 PM

I'm not sure how to get the worksheet name, but to get the highest amount, you could try something like this

=MAX(Jan!C14,Feb!C14,Mar!C14,Apr!C14,May!C14,Jun!C14,Jul!C14,Aug!C14,Sept!C14,Oct!C14,Nov!C14,Dec!C14)

If you sheets have long "month names" modify to suit.

**vane0326** · 04-30-2007, 10:28 PM

Try listing your worksheets in column A starting with cell A2:A13.
This formula below will return the worksheet name. Input formula in cell C2.

=INDEX($A$2:$A$13,MATCH(TRUE,COUNTIF(INDIRECT("'"&$A$2:$A$13&"'!C14"),B2)>0,0))

The formula is an-arrays need to hold down:

Ctrl,Shft,Enter

Example workbook below.