+ Reply to Thread
Results 1 to 12 of 12

Need worksheet name for Highest value of a single cell across multiple worksheets

  1. #1
    Registered User
    Join Date
    10-02-2020
    Location
    Lansing, Michigan
    MS-Off Ver
    365 ProPlus
    Posts
    8

    Need worksheet name for Highest value of a single cell across multiple worksheets

    I have a State to State price matrix for transportation discounts by lanes between states. I've been able to get the max value utilizing the following. =MAX('Carrier1:Carrier8'!K4:K4) in each cell within the "Main Eval Discount" worksheet for best discount.

    In the next matrix worksheet "Main Eval Carrier" I need to identify what carrier sheet (i.e. Carrier Name) corresponds to that highest discount in each cell.

    The matrix is 147x147 cell square.

    I've seen several examples for ranges but can't seem to get them to apply to my situation. Obviously operator challenged.

    Any help will be very much appreciated.

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Need worksheet name for Highest value of a single cell across multiple worksheets

    Hi,

    Difficult to be sure without seeing an actual workbook, though I would think that:

    ="Carrier"&MATCH(1,FREQUENCY(MAX(Carrier1:Carrier8!K4),Carrier1:Carrier8!K4),0)

    would give you your desired result.

    Or, since you have already determined the maximums in the Main Eval Discount worksheet:

    ="Carrier"&MATCH(1,FREQUENCY('Main Eval Discount'!K4,Carrier1:Carrier8!K4),0)

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    10-02-2020
    Location
    Lansing, Michigan
    MS-Off Ver
    365 ProPlus
    Posts
    8

    Re: Need worksheet name for Highest value of a single cell across multiple worksheets

    Close

    ="Carrier"&MATCH(1,FREQUENCY('MAIN Eval Discount'!K4,'A Duiepyle:JJT'!K4),0)

    Returns the following: "Carrier2"

    I was hoping to have "SAIA" returned.

    If the screen shot came through it will show the "Main Eval Discount" tab and the various Carrier Tabs

    Attachment 698004

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Need worksheet name for Highest value of a single cell across multiple worksheets

    Quote Originally Posted by Tristam88 View Post
    I've been able to get the max value utilizing the following. =MAX('Carrier1:Carrier8'!K4:K4) in each cell within the "Main Eval Discount" worksheet for best discount.
    Which implies that the leftmost and rightmost of the worksheets in question are named Carrier1 and Carrier8 respectively.

    Quote Originally Posted by Tristam88 View Post
    ="Carrier"&MATCH(1,FREQUENCY('MAIN Eval Discount'!K4,'A Duiepyle:JJT'!K4),0)

    Returns the following: "Carrier2"

    I was hoping to have "SAIA" returned.
    Though this implies that that assumption re the worksheets in question is not correct.

    Can you clarify? We can only go on the information you provide.

    Regards

  5. #5
    Registered User
    Join Date
    10-02-2020
    Location
    Lansing, Michigan
    MS-Off Ver
    365 ProPlus
    Posts
    8

    Re: Need worksheet name for Highest value of a single cell across multiple worksheets

    Understood. My error, as I am new to this and didn't use that actual names of the tabs in my first request.


    So what I have is 8 tabs that have been named for each specific carrier that submitted discounts. I have identified the highest discount.
    i.e. "73" in the "MAIN Eval Discount" tab/worksheet

    What I'm trying to determine in the "MAIN Eval Carrier" tab/spreadsheet is how to have the name of the corresponding tab/worksheet Name (also that of the carrier) from the the highest discount is located.
    i.e. the "73" discount for "K4" across all the tab/spreadsheets is from the tab labeled "SAIA", and would like it to appear.

    Your patience with me is very much appreciated as I recognize that I wasn't clear in my original explanation.

    Hopefully the screenshot of the workbook I'm working from and the above will help clarify the situation and what I'm looking for.

    Thanks again.

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Need worksheet name for Highest value of a single cell across multiple worksheets

    No worries.

    The screenshot you posted doesn't appear to be a valid attachment. You can attach actual Excel workbooks by following the instructions in the yellow box at the top of this page.

    However, that might not be necessary if you can just clarify the names of the leftmost and rightmost of the 8 worksheets to be considered.

    Regards

  7. #7
    Registered User
    Join Date
    10-02-2020
    Location
    Lansing, Michigan
    MS-Off Ver
    365 ProPlus
    Posts
    8

    Re: Need worksheet name for Highest value of a single cell across multiple worksheets

    "A Duiepyle" and "JJT"

  8. #8
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Need worksheet name for Highest value of a single cell across multiple worksheets

    Thanks.

    For a non-macro-enabled workbook, I can't quite get it to work given the leftmost and rightmost worksheet names only.

    However, if you go to Name Manager and define an array comprising those eight sheet names in the order that they appear within the workbook from left to right, for example:

    Name: SheetNames
    Refers to: ={"A Duiepyle","A","B","C","D","E","F","JJT"}

    The required formula is then

    =INDEX(SheetNames,MATCH(1,FREQUENCY('Main Eval Discount'!K4,'A Duiepyle:JJT'!K4),0))

    Regards

  9. #9
    Registered User
    Join Date
    10-02-2020
    Location
    Lansing, Michigan
    MS-Off Ver
    365 ProPlus
    Posts
    8

    Re: Need worksheet name for Highest value of a single cell across multiple worksheets

    Thanks for your assistance!

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742
    You're welcome!

    Regards

  11. #11
    Registered User
    Join Date
    10-02-2020
    Location
    Lansing, Michigan
    MS-Off Ver
    365 ProPlus
    Posts
    8

    Re: Need worksheet name for Highest value of a single cell across multiple worksheets

    Question as I think I missed something.

    In Names Manager I created "SheetNames" and typed the following in Refers to: ={"A Duiepyle","UPS","Averitt","SAIA","ABF","Pitt Ohio","Dayton","New Penn","Holland","Reddaway","JJT"}

    Then in the "MAIN Eval Carrier" tab in cell K4 I pasted the following =INDEX(SheetNames,MATCH(1,FREQUENCY('MAIN Eval Discount'!K4,'A Duiepyle:JJT'!K4),0))

    It returned "UPS" but "SAIA" has the highest discount? The UPS discount is 72 while SAIA is 73

    In the "MAIN Eval Discount" tab the value in K4 is 73 the SAIA value.

    What am I missing :-(

  12. #12
    Registered User
    Join Date
    10-02-2020
    Location
    Lansing, Michigan
    MS-Off Ver
    365 ProPlus
    Posts
    8

    Re: Need worksheet name for Highest value of a single cell across multiple worksheets

    I think I may have figured it out. Some of the cells have nothing in them and it seams that if I put a zero in them then the formula works. Does that sound correct?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 01-29-2020, 05:19 PM
  2. Pasting Data From Multiple Worksheets into a single worksheet
    By Don Adeks in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-12-2019, 11:17 AM
  3. Trying to copy multiple worksheets into one single worksheet
    By MAX258 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-26-2017, 01:44 PM
  4. Copy multiple lists from multiple worksheets into single worksheet
    By gowtham_pec in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-01-2013, 04:44 AM
  5. Copying multiple rows from multiple worksheets to a single worksheet
    By Malkave in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-09-2012, 06:35 AM
  6. Append multiple worksheets into single worksheet
    By steve-waters in forum Excel General
    Replies: 1
    Last Post: 01-02-2008, 06:11 AM
  7. [SOLVED] Need to have multiple worksheets use a single worksheet
    By storm5527 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-22-2005, 07:06 AM

Tags for this Thread

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