Hello,
I am using the following formula to get maximum value from a particular cell across 97 different sheets with name variation. This works fine. How can I get the sheet name from which the maximum value is coming?
Best, Sushant
=MAX(MAX('LK1-beam:LK9-beam'!Q10),MAX('LK10-column:LK97-column'!Q10))
1st attach workbook dear
need excel file 2 see what we can do 4 u
If you have that many sheets, I would probably make a summary sheet where I pull in the value from each sheet (name), determine the max from that, and then, because you will already have all the sheet names, it would be simple to pull out the sheet name from that list
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Assuming your sheet names follow a consistent naming pattern, i.e.:
LK1-beam, LK2-beam, LK3-beam,..., LK9-beam
LK10-column, LK11-column, LK12-column,..., LK97-column
then:
=IF(MAX('LK1-beam:LK9-beam'!Q10)>MAX('LK10-column:LK97-column'!Q10),"LK"&MATCH(1,FREQUENCY(MAX('LK1-beam:LK9-beam'!Q10),'LK1-beam:LK9-beam'!Q10),0)&"-beam","LK"&9+MATCH(1,FREQUENCY(MAX('LK10-column:LK97-column'!Q10),'LK10-column:LK97-column'!Q10),0)&"-column")
though you gave no indication of which sheet should be preferred if the maximum is shared by one or more sheets.
Regards
You can adapt this.
Sheet 1 A3 returns the maximum value from the same range across 3 sheets.
Sheet 1 I1 to I150 uses an inbuilt Excel function (must be macro enabled) to return a list of sheets in the workbook minus the one on which the list is created. There is a Named Range working here, too. CTRL F3. It is called Sheets.
Sheet 1 B3 returns the sheet name, using another named range (ShtList) which is a dynamic list of sheet names, currently set to a mximum of 150. It's an array formula (set with CTRL-SHIFT-ENTER).
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi Glenn Kennedy,
Just wondering why you saved the file as xlsm, because I could not able to find any code
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Hi. 'cos of the Excle 4.0 function used to get the list of sheetnames in the named range (Sheets):
=TRANSPOSE(GET.WORKBOOK(1))&T(NOW())
Oopss.. Sorry for that since I have not paid full attention on your all formula's.Originally Posted by Glenn Kennedy
I thought those sheet name arriving column was used as a manual input area.
Thanks for the clarification
Here's a formula method to generate the sheet names:
https://www.excelforum.com/showthread.php?t=929969
Since it uses an Excel4 macro function the file must be saved as a macro enabled file in Excel versions 2007 or later.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi all,
Thanks for replying. I found the answer here-
http://www.mrexcel.com/forum/excel-q...max-value.html
See the comment by Domenic.
Best regards
Did you try the solution that i suggested?
Dear Glenn,
I haven't tried it yet as I found the answer in Domenic's post.
Best regards,
Sushant
Sg_89, this is exactly why we - and most other forums - insist that if you post the same question on another forum, you include a link to that cross-post.
That way, members dont waste their time and effort on your question, when you have already got the answer elsewhere!!
FDibbins, I am sorry for this and would make sure that next time I include a link to the cross-post.
Best regards,
Sushant
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks