4,375 questions
You need to enclose the sheet names in single quotation marks. My system does not accept semicolons as argument separators. Use commas.
=LARGE(('Sheet1'!A5,'Sheet2'!A5,...),2)
How to find the 2nd largest number in cells that are not row in the same column
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(313.6, 6%, 40%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Νικόλαος Γιαννέλος
0
Reputation points
Good evening to all,
In an Excel workbook, to find the second largest number in cells that are not
row in the same column, we use the formula : =LARGE((A10;B15;D12);2)
Now when we want to find the second largest number in cells in different sheets,
we use the formula : =LARGE((Sheet1!A5;Sheet2!A5;Sheet3!A5);2) but it doesn't work!
Is there something else?
Thanks in advance
Microsoft 365 and Office Development Other
Microsoft 365 and Office Install, redeem, activate For business Windows
Microsoft 365 and Office Excel For business Windows
3,888 questions
{count} votes
-
Viorel 122.6K Reputation points2023-09-22T05:06:12.37+00:00 To work around the problem, it is possible to fill a range (for example: Sheet5!K1:K3) with the values from other sheets using expressions like
=Sheet1!A5. Then=LARGE(Sheet5!K1:K3;2)seems to work. The K column or Sheet5 can be hidden.If Sheet2 is between Sheet1 and Sheet3, then try a “3-D” reference:
=LARGE(Sheet1:Sheet3!A5;2).In new Excel, this workaround seems to work too:
=LARGE(MAKEARRAY(1;3; LAMBDA(r;c; CHOOSE(c;Sheet1!A5;Sheet2!A5;Sheet3!A5))); 2). -
Emi Zhang-MSFT 30,046 Reputation points Microsoft External Staff2023-09-22T07:22:56.2266667+00:00 Hi,
Just checking in to see if the information was helpful. Please let us know if you would like further assistance.
If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
-
Νικόλαος Γιαννέλος 0 Reputation points
2023-09-22T19:39:58.08+00:00 Viorel thank you very much for your time !!!
The formula =LARGE(Sheet1:Sheet3!A5;2) etc =LARGE(Sheet1:Sheet10!A5;2) works perfect. So simple and fast when someone shows that.
I didn't check the other formula, because to say the truth i didn't quite understand that =LARGE(MAKEARRAY(1;3; LAMBDA(r;c; CHOOSE(c;Sheet1!A5;Sheet2!A5;Sheet3!A5))); 2)
Thanks again for your answer and for your time
Sign in to comment
1 answer
Sort by: Most helpful
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 53%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
Barry Schwarz 3,746 Reputation points2023-09-21T19:33:02.1233333+00:00 -
Νικόλαος Γιαννέλος 0 Reputation points
2023-09-21T21:10:13.7266667+00:00 Nope, its not working! The funny thing is that it works with =MAX('Sheet1'!A5;'Sheet2'!A5';Sheet3'!A5;...) so its not about semicolons or commas. Its something else.
Thank you for your answer and for your time
-
Barry Schwarz 3,746 Reputation points
2023-09-21T23:08:47.8833333+00:00 My example worked for my test case. Please copy and paste the exact formula you are using.
-
Νικόλαος Γιαννέλος 0 Reputation points
2023-09-22T04:49:23.4466667+00:00 The formula im using is =LARGE(('Sheet1'!A28;'Sheet2'!A28;'Sheet3'!A28;'Sheet4'!A28;'Sheet5'!A28;'Sheet6'!A28;'Sheet7'!A28;'Sheet8'!A28);2) and when i hit enter the cell says #value!
If i put your formula with commas
=LARGE(('Sheet1'!A28,'Sheet2'!A28,'Sheet3'!A28,'Sheet4'!A28,'Sheet5'!A28,'Sheet6'!A28,'Sheet7'!A28,'Sheet8'!A28),2) and hit enter says
There's a problem with this formoula
Not trying to type a formula?
...
-
Barry Schwarz 3,746 Reputation points
2023-09-22T07:38:17.35+00:00 I have no idea why semicolons work on your system but commas don't and it is the exact opposite on my system.But that is not the real issue.
The fact that the formula returns #VALUE implies that one of the eight specified cells either doesn't exist or already contains the #VALUE or some other error condition.
-
Νικόλαος Γιαννέλος 0 Reputation points
2023-09-22T19:47:05.47+00:00 Barry Schwarz ,Viorel find it! Formula =LARGE(Sheet1:Sheet10!A5;2) works perfect. Thank you both
Sign in to comment -