# Finding the highest value across worksheets and returning the worksheet name

1. ## Finding the highest value across worksheets and returning the worksheet name

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.

Darlo

2. 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.

3. 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.

4. Excellent thank you!

I've modified these slightly and they work perfectly.

Thanks for the starting point.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1