+ Reply to Thread
Results 1 to 4 of 4

Finding the highest value across worksheets and returning the worksheet name

  1. #1
    Registered User
    Join Date
    03-09-2007
    Posts
    51

    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.

    Thanks in advance.

    Darlo

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    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.
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269
    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.
    Attached Files Attached Files
    Last edited by vane0326; 04-30-2007 at 10:30 PM.

  4. #4
    Registered User
    Join Date
    03-09-2007
    Posts
    51
    Excellent thank you!

    I've modified these slightly and they work perfectly.

    Thanks for the starting point.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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