+ Reply to Thread
Results 1 to 7 of 7

Search for minimum value in a particular cell across 500 sheets.

  1. #1
    Registered User
    Join Date
    02-23-2014
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Search for minimum value in a particular cell across 500 sheets.

    Hi,

    I am actually trying to get the minimum value for a particular entry across 500 sheets, I am not really sure how to go about it, but i tried some MIN(A:ZTS!) formula and got an error. Basically this did not solve my problem.

    Some more details:
    I have a value in cell O11 across 500 sheets. I want to compare through all the sheets and find the minimum value. Also i need to know from which sheet does that minimum value come from.

    This cell O11 has a result that has been calculated using a formula and data on that particular sheet.

    Please let me know if i need to explain anything more. I am relatively new to working with multiple sheets and vba and my first assignment is with 500 sheets.

    Thank you for the help in advance.
    Last edited by mananshah90; 02-24-2014 at 08:54 PM. Reason: SOLVED

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,313

    Re: Search for minimum value in a particular cell across 500 sheets.

    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    02-23-2014
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Search for minimum value in a particular cell across 500 sheets.

    This does not work. It gives #NAME?? error
    i would be extracting my answer in a seperate sheet say like master sheet.

    i also tried this: =MIN(A:ZTS!O10) but then i got a #NUM! error in the cell

    Quote Originally Posted by protonLeah View Post
    Please Login or Register  to view this content.
    Last edited by mananshah90; 02-24-2014 at 12:32 AM.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,313

    Re: Search for minimum value in a particular cell across 500 sheets.

    The formula does work, I tested it. Evidently your tabs are not named sheet1,..., sequentially.

    An alternative: Select all the desired tabs and, click O11 and define a name, say, Cell_O11. Then
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-23-2014
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Search for minimum value in a particular cell across 500 sheets.

    ohh yes... my sheets are named according to tickers of snp500 stocks....

    so how to work about in this case?

  6. #6
    Registered User
    Join Date
    02-23-2014
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Search for minimum value in a particular cell across 500 sheets.

    Hey,

    Thank you so much for your help. I did figure out a way for the time being. It is a little incorrect but thats ok.

    Got help from this post:
    http://www.excelforum.com/excel-form...le-sheets.html

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,927

    Re: Search for minimum value in a particular cell across 500 sheets.

    See attached.

    "Team" sheet finds the minimum score per whole achieved over all players, represented by Sheet2 and Sheet3 (2 players). The Week figures exclude the handicap but could easily be included - as per formula for "Best Score".

    The formula works by including sheets in a range. a Good trick is to add a (empty) sheet called "First" before your player sheets and one called "Last" after the last player sheet.

    The formulae then become MIN(First:Last! C17 etc) which allows you to add/delete players without needing to change the formulae in "Team"

    Please ignore!!!
    Attached Files Attached Files
    Last edited by JohnTopley; 07-11-2015 at 04:35 PM. Reason: Wrong post

+ 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: 7
    Last Post: 04-17-2013, 03:53 PM
  2. Replies: 1
    Last Post: 07-17-2012, 05:28 AM
  3. Search up/down from n'th term and retrieve value if minimum difference
    By scope951 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-26-2010, 07:16 AM
  4. Minimum search in the next 15 entries
    By BLopes in forum Excel General
    Replies: 4
    Last Post: 05-13-2010, 03:39 PM
  5. search for cell info between 2 sheets
    By ROCKCHOPPER in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-31-2008, 07:45 PM

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