• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Locate the Cell which contains the MAX.

C

Certificates!

Member
Three days ago I posted this query on MR Excel where I have subscribed for several years. I di not get a reply other than to read a web page which was far above my knowledge level. So hoepfully I will have more luck.

I have twelve monthly worksheets with amongst other things, the number of entrants to a competition in cells J3: J124 in each worksheet.

Logically the worksheets are called Prizes October 2021 ..... prizes September 2022.

I have a subsequent summary sheet and have pulled through the maximum number from all twelve columns.

=MAX('Prizes October 2021 Prizes September 2022'!J3:J124,1) It is in cell L8 on the summary sheet.

What I would really like is Cell J8 on the Summary Sheet to show the detail contained in the cell next to the one containing the maximum number. It will be in Column I but could be on any sheet, and could be any cell.

Is this feasible? If it isn't is it possible to simply identify the source cell of the one containing the maximum value - Worksheet an Cell Reference.

Whilst Excel isn't new to me, I really only use the mathematical side and Match/Lookupetc and something I have limited experience of.

Thanks in advance
 
Last edited:
There's only a limited number of functions which work with 3d references (that's what you're using).
I think what you want may be able to be done with a UDF (a function we define that can be used like a normal worksheet function) but this requires the workbook to be macro-enabled; is this an option?
 
I don't know.

It's currently on Office 365, but I can put it on Excel 2013. There aren't any company restrictions or anything like that, if that is what you mean.

Thanks for your response.
 
Some of the latest array shaping functions available within 365 are pretty remarkable. In particular VSTACK will append data from a 3D range such as
Code: 
tables
= Jan:Mar!$B$4:$D$9

formula
= LET(
    combined, VSTACK(tables),
    values,   TAKE(combined,,-1),
    FILTER(combined, values=MAX(values))
  )
80413
 

Attachments

  • SummaryPage.xlsx
    15.6 KB · Views: 4
Here is another 3D Lookup formula option, work for Excel 2019 and above versions

1] In Summary sheet add a column of S/n in A3:A8 --> 1 to 6

2] In Summary sheet D3, formula copied down :

=IF(LARGE(Jan:Mar!D$3:D$8,A3)=MAX(Jan:Mar!D$3:D$8),MAX(Jan:Mar!D$3:D$8),"")

3] In Summary sheet B3, formula copied right to C3 and all copied down :

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,Jan:Mar!B$3:$D$8)&"</b></a>","//b[following::*["&3-COLUMN(A$1)&"]='"&$D3&"']["&ROW($A1)&"]"),"")

4] Hit keystroke F9, to see the change of Detail, Month & Max value

5] Please see attachment file

80415
 

Attachments

  • 3D lookup (BY).xlsx
    15.9 KB · Views: 3
Last edited:
Peter Bartholomew said:
Some of the latest array shaping functions available within 365 are pretty remarkable. In particular VSTACK will append data from a 3D range such as
Code: 
tables
= Jan:Mar!$B$4:$D$9

formula
= LET(
    combined, VSTACK(tables),
    values,   TAKE(combined,,-1),
    FILTER(combined, values=MAX(values))
  )
View attachment 80413
Click to expand...
This seems very good, but my Excel 365 does not seem to have this upgrade. I guess it's time to contact the Group administrator to get it updated.

Thanks very much for your input.
 
bosco_yip said:
Here is another 3D Lookup formula option, work for Excel 2019 and above versions

1] In Summary sheet add a column of S/n in A3:A8 --> 1 to 6

2] In Summary sheet D3, formula copied down :

=IF(LARGE(Jan:Mar!D$3:D$8,A3)=MAX(Jan:Mar!D$3:D$8),MAX(Jan:Mar!D$3:D$8),"")

3] In Summary sheet B3, formula copied right to C3 and all copied down :

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,Jan:Mar!B$3:$D$8)&"</b></a>","//b[following::*["&3-COLUMN(A$1)&"]='"&$D3&"']["&ROW($A1)&"]"),"")

4] Hit keystroke F9, to see the change of Detail, Month & Max value

5] Please see attachment file

View attachment 80415
Click to expand...
Thanks for this. It's a bit complex for me, and I may not be able to use the cells you have suggested, but I'll give it a shot and let you know.

Will it work over several worksheets?
 
Certificates!
You should reread Forum Rules - those are for You too

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
  • Like
Reactions: Hui
1. In the attached on the Summary sheet in cell L8 a MAX formula; not sure why there is a final 1 in your formula in msg#1 here
Certificates! said:
=MAX('Prizes October 2021 Prizes September 2022'!J3:J124,1)
Click to expand...
so I've removed it. If it needs to be there, tell me and I'll tweak the UDF to accommodate.
In cell J8 there's a formula:
=WheresWally(L8)
What this udf actually does is to look at the text of the formula in cell L8 and uses it to determine which sheets and what range on each sheet the formula is looking at, then the code looks at each cell in those ranges, see if it's equal to the value in L8 and if it is returns the text from the cell to the left. Should there be more than one cell equal to the max, it will return the contents of all such cells on a new line within the cell with WheresWally formula in; you may need to allow wordwrap in that cell in order to see all of them.
It's ham-fisted. I haven't streamlined the code (it's in the Module1 code module) which makes it easier to follow and tweak if necessary.

2. I've also added a sheet called bosco_yip where I've started to implement @bosco_yip 's solution but haven't been able finish it properly. Perhaps bosco will finish it for me.

3. Regarding my asking about macro enabled workbooks, it was mainly about whether your company would restrict that (quite a lot do). The UDF function should work on all versions at least from Excel 2013 if not before.

4. @Peter Bartholomew will be heartened with this bit of news: when I wrote:
p45cal said:
There's only a limited number of functions which work with 3d references
Click to expand...
I realised that VSTACK and HSTACK were among them but at the time of writing they weren't available to me on my account which is Office365 Current Channel (Preview), but this morning I was told there were updates available, I updated and hey ho, VSTACK and HSTACK are available to me.
However, I doubt most companies are on the Preview version (someone may well prove me wrong) so asking your Group Administrator to upgrade may not help right now.

5. You'll see that the attached tries to replicate your spreadsheet according to your description in msg#1. It would encourage the people you're asking for help from if you were to provide such a workbook yourself. Not many will be prepared to spend the time to prepare such a workbook. Besides I might have guessed all wrong.

6. Following cross-posting rules does matter. (I, for one, don't help again if a member, despite being told, doesn't include cross post links in subsequent requests for help)
 

Attachments

  • Chandoo48575.xlsm
    58 KB · Views: 3
pecoflyer said:
Cross post https://www.mrexcel.com/board/threads/locate-the-cell-with-the-maximum-value.1212660/
and
https://www.excelforum.com/excel-fo...ess-from-multiple-worksheets.html#post5709128 ( thread closed OP does not accept remarks about cross posting)
Click to expand...
I said at the top of the post that I had cross posted to Mr Excel but didn't get a response in three days. Excel Formula sent me two unfriendly notes withing one minute of posting saying do this, don't do that. That forum wasn't for me and the thread was closed within five minutes of it being opened.
 
vletm said:
Certificates!
You should reread Forum Rules - those are for You too

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
Click to expand...
vletm said:
Certificates!
You should reread Forum Rules - those are for You too

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
Click to expand...
I opened my query with: Three days ago I posted this query on MR Excel where I have subscribed for several years. I di not get a reply other than to read a web page which was far above my knowledge level. So hoepfully I will have more luck.

Could I have made it plainer?
 
p45cal said:
1. In the attached on the Summary sheet in cell L8 a MAX formula; not sure why there is a final 1 in your formula in msg#1 hereso I've removed it. If it needs to be there, tell me and I'll tweak the UDF to accommodate.
In cell J8 there's a formula:
=WheresWally(L8)
What this udf actually does is to look at the text of the formula in cell L8 and uses it to determine which sheets and what range on each sheet the formula is looking at, then the code looks at each cell in those ranges, see if it's equal to the value in L8 and if it is returns the text from the cell to the left. Should there be more than one cell equal to the max, it will return the contents of all such cells on a new line within the cell with WheresWally formula in; you may need to allow wordwrap in that cell in order to see all of them.
It's ham-fisted. I haven't streamlined the code (it's in the Module1 code module) which makes it easier to follow and tweak if necessary.

2. I've also added a sheet called bosco_yip where I've started to implement @bosco_yip 's solution but haven't been able finish it properly. Perhaps bosco will finish it for me.

3. Regarding my asking about macro enabled workbooks, it was mainly about whether your company would restrict that (quite a lot do). The UDF function should work on all versions at least from Excel 2013 if not before.

4. @Peter Bartholomew will be heartened with this bit of news: when I wrote:
I realised that VSTACK and HSTACK were among them but at the time of writing they weren't available to me on my account which is Office365 Current Channel (Preview), but this morning I was told there were updates available, I updated and hey ho, VSTACK and HSTACK are available to me.
However, I doubt most companies are on the Preview version (someone may well prove me wrong) so asking your Group Administrator to upgrade may not help right now.

5. You'll see that the attached tries to replicate your spreadsheet according to your description in msg#1. It would encourage the people you're asking for help from if you were to provide such a workbook yourself. Not many will be prepared to spend the time to prepare such a workbook. Besides I might have guessed all wrong.

6. Following cross-posting rules does matter. (I, for one, don't help again if a member, despite being told, doesn't include cross post links in subsequent requests for help)
Click to expand...
Thanks for you response, I'll work at it. I did say though that I had cross posted. My request opens with: I opened my query with: Three days ago I posted this query on MR Excel where I have subscribed for several years. I di not get a reply other than to read a web page which was far above my knowledge level. So hoepfully I will have more luck.
 
p45cal said:
1. In the attached on the Summary sheet in cell L8 a MAX formula; not sure why there is a final 1 in your formula in msg#1 hereso I've removed it. If it needs to be there, tell me and I'll tweak the UDF to accommodate.
In cell J8 there's a formula:
=WheresWally(L8)
What this udf actually does is to look at the text of the formula in cell L8 and uses it to determine which sheets and what range on each sheet the formula is looking at, then the code looks at each cell in those ranges, see if it's equal to the value in L8 and if it is returns the text from the cell to the left. Should there be more than one cell equal to the max, it will return the contents of all such cells on a new line within the cell with WheresWally formula in; you may need to allow wordwrap in that cell in order to see all of them.
It's ham-fisted. I haven't streamlined the code (it's in the Module1 code module) which makes it easier to follow and tweak if necessary.

2. I've also added a sheet called bosco_yip where I've started to implement @bosco_yip 's solution but haven't been able finish it properly. Perhaps bosco will finish it for me.

3. Regarding my asking about macro enabled workbooks, it was mainly about whether your company would restrict that (quite a lot do). The UDF function should work on all versions at least from Excel 2013 if not before.

4. @Peter Bartholomew will be heartened with this bit of news: when I wrote:
I realised that VSTACK and HSTACK were among them but at the time of writing they weren't available to me on my account which is Office365 Current Channel (Preview), but this morning I was told there were updates available, I updated and hey ho, VSTACK and HSTACK are available to me.
However, I doubt most companies are on the Preview version (someone may well prove me wrong) so asking your Group Administrator to upgrade may not help right now.

5. You'll see that the attached tries to replicate your spreadsheet according to your description in msg#1. It would encourage the people you're asking for help from if you were to provide such a workbook yourself. Not many will be prepared to spend the time to prepare such a workbook. Besides I might have guessed all wrong.

6. Following cross-posting rules does matter. (I, for one, don't help again if a member, despite being told, doesn't include cross post links in subsequent requests for help)
Click to expand...
Here is one of the worksheets from which I am trying to locate the cell. I have twelve of them, exactly the same. I can pull the highest number from the twelve column Js but I'd like to identify the source.
 

Attachments

  • Entrants.xlsx
    12 KB · Views: 3
@p45cal

1] Maybe the limitation of Textjoin function, the formula only can summary for 7 worksheets (122*7*2=1708 cells)

2] Please see attached file (bosco_yip sheet)

Remark : I think 3-D R1C1 multiple columns formula maybe less limitation, I am now in busy and will be tested of it by tomorrw.

bosco_yip
 

Attachments

  • Chandoo48575 (Bosco).xlsm
    58.2 KB · Views: 4
Last edited:
Certificates!
Your Could I have made it plainer?
Yes.
You gave a reason, why You didn't get replies.
Is it ... unfriendly ... if somebody try to teach You, what to do?
All Forums has basically same rules with Cross-Posting and many other things too.
Those rules are for everybody.
Everybody should read those basic rules before start posting.
After that You Cross-Posted in other Forum too.
 
vletm said:
Certificates!
Your Could I have made it plainer?
Yes.
You gave a reason, why You didn't get replies.
Is it ... unfriendly ... if somebody try to teach You, what to do?
All Forums has basically same rules with Cross-Posting and many other things too.
Those rules are for everybody.
Everybody should read those basic rules before start posting.
After that You Cross-Posted in other Forum too.
Click to expand...
I did not.
 
Please can this thread be closed. Thanks for your help, some of which was beyond me, however I seem to have caused a hornets' nest to have opened. I thought I had done the right thing by saying I had crossed posted a couple of days ago and not got any response other than something that was beyond me.

I am grateful, but don't want to upset anyone.
 
vletm said:
Certificates!
You should reread Forum Rules - those are for You too

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
Click to expand...
Thankyou VLetm

Cross Posting is generally considered poor form, as people don't follow up and close of solutions on all forums.
This can cause people to both waste time, and miss great solutions

I am sure the OP will not do that here ?
 
Certificates! said:
I have twelve of them, exactly the same. I can pull the highest number from the twelve column Js but I'd like to identify the source.
Click to expand...
Yep, that's what I did.
In the code for the UDF, there are two lines starting ThisWally = , one is commented-out (disabled) by an apostrophe at its start.
If you go into the code and move the apostrophe from the beginning of one to the beginning of the other (this just changes which of those two lines is 'live' (only one of them should be)), it will give you a slightly different result on the sheet, one which adds the sheet name(s) and cell address(es) of the max value(s). This should show you the source, but I thought you said you wanted the contents of the adjacent cell.
 
p45cal said:
Yep, that's what I did.
In the code for the UDF, there are two lines starting ThisWally = , one is commented-out (disabled) by an apostrophe at its start.
If you go into the code and move the apostrophe from the beginning of one to the beginning of the other (this just changes which of those two lines is 'live' (only one of them should be)), it will give you a slightly different result on the sheet, one which adds the sheet name(s) and cell address(es) of the max value(s). This should show you the source, but I thought you said you wanted the contents of the adjacent cell.
Click to expand...

Actual the OP wanted the contents of the adjacent 4 columns with value cell, please see level 5 of the OP posted table in the other forum :

LOCATE THE CELL WITH THE MAXIMUM VALUE | MrExcel Message Board

bosco_yip
 
bosco_yip said:
Actual the OP wanted the contents of the adjacent 4 columns with value cell
Click to expand...
Mmmm, I'm not so sure, there I think he's just showing a screenshot of the vicinity. I was following from msg#1 here:
Certificates! said:
really like is Cell J8 on the Summary Sheet to show the detail contained in the cell next to the one containing the maximum number. It will be in Column I but could be on any sheet, and could be any cell
Click to expand...
, but now that I've looked at that crosspost I see:
If I can do that I'd like the information contained in K63 to be available next to the cell reference.
Click to expand...
from which it looks like he wants column K!
I'll let him experiment, it can be K or I by tweaking:
cll.Offset(, -1).Value
to:
cll.Offset(, 1).Value
in my code.
 
Thanks very much to all of you for your efforts. I have solved this problem. Rather more cumbersomely than the elegant solutions you suggested to me though some of what you suggested was beyond me abilitiy level, However what I have done does the trick.

On the summary sheet I created formulas in columns B11:B22 to identify the highest number of entrants: MAX('Prizes October 2021'!J3:J30)
and then using nested if functions determined the name of the competition with the highest entry. There were twelve of these, one for each month.

=IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J3,'Prizes October 2021'!I3,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J4,'Prizes October 2021'!I4,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J5,'Prizes October 2021'!I5,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J6,'Prizes October 2021'!I6,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J7,'Prizes October 2021'!I7,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J8,'Prizes October 2021'!I8,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J9,'Prizes October 2021'!I9,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J10,'Prizes October 2021'!I10,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J11,'Prizes October 2021'!I11,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J12,'Prizes October 2021'!I12,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J13,'Prizes October 2021'!I13,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J14,'Prizes October 2021'!I14,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J15,'Prizes October 2021'!I15,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J16,'Prizes October 2021'!I16,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J17,'Prizes October 2021'!I17,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J18,'Prizes October 2021'!I18,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J19,'Prizes October 2021'!I19,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J20,'Prizes October 2021'!I20,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J21,'Prizes October 2021'!I21,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J22,'Prizes October 2021'!I22,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J23,'Prizes October 2021'!I23,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J24,'Prizes October 2021'!I24,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J25,'Prizes October 2021'!I25,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J26,'Prizes October 2021'!I26,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J27,'Prizes October 2021'!I27,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J28,'Prizes October 2021'!I28,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J29,'Prizes October 2021'!I29,IF(MAX('Prizes October 2021'!J3:J30)='Prizes October 2021'!J30,'Prizes October 2021'!I30,""))))))))))))))))))))))))))))


I then used the same system to identify the name of the most popular competition from column C11:C22 on the summary sheet.
=IF(MAX(B11:B22)=B11,C11,IF(MAX(B11:B22)=B12,C12,IF(MAX(B11:B22)=B13,C13,IF(MAX(B11:B22)=B14,C14,IF(MAX(B11:B22)=B15,C15,IF(MAX(B11:B22)=B16,C16,IF(MAX(B11:B22)=B17,C17,IF(MAX(B11:B22)=B18,C18,IF(MAX(B11:B22)=B19,C19,IF(MAX(B11:B22)=B20,C20,IF(MAX(B11:B22)=B21,C21,IF(MAX(B11:B22)=B22,C22,""))))))))))))


As I said very cumbersome, but it works fine.

Thanks again for the help and I'm sorry if I caused upset to anyone. It wasn't my intention.
 
Hi Certificates!

We are pleased to hear that you can solve your problem by yourself.

So, don't worry, you have achieved your first step, and let us keep Excelling.............

bosco_yip
 
You must log in or register to reply here.
Back
Top