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

1. ## 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.

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

``Please Login or Register  to view this content.``

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

Originally Posted by protonLeah
``Please Login or Register  to view this content.``

4. ## 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.``

5. ## 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. ## 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. ## 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"

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