Using MAX (or similar) over multiple worksheets

cidgreen

New Member
Joined
Aug 26, 2002
Messages
22
Hello there,

I am trying to do what I think is a simple task, but I'm struggling considerably.

I have a file with five worksheets in it. The first four sheets each contain three columns of numbers.

I need to find the maximum number in the second column of all the sheets and have it outputted in the fifth sheet.

In other words, if the biggest number in column B of any of the four sheets is 20, I need 20 to appear in a cell in the fifth sheet.

Can anyone tell me how to do this? This is a simple example so if someone could show me how to do this I would be able to tailor it to my (much larger) sheets of stuff.

Any help, as always, will be very gratefully received.

Thanks in advance,
Chris.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
If you do an array formula, it should give you what you are trying to accomplish. {=max(Sheet1range,sheet2range....)} will return the max value in your selected ranges.
 

cidgreen

New Member
Joined
Aug 26, 2002
Messages
22
I sometimes marvel at my own idiocy - it was one of the things I tried first but I had the ranges as B:B instead of B1:B200 and so on.

If you don't mind me asking Aaron (and anyone else reading it), can you think of a way that I could then reference back into the sheet that the max number is in, and return the value in Column A next to it?

The max number is a number of goals scored and the player's names are in the column A's - is there a way to find out, from the max number, exactly which player has the most goals in the four sheets?

And, of course, thanks very much indeed for your help (and sorry for asking such a feeble question first off...)

Cheers,
Chris.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-11-05 11:15, cidgreen wrote:
Hello there,

I am trying to do what I think is a simple task, but I'm struggling considerably.

I have a file with five worksheets in it. The first four sheets each contain three columns of numbers.

I need to find the maximum number in the second column of all the sheets and have it outputted in the fifth sheet.

In other words, if the biggest number in column B of any of the four sheets is 20, I need 20 to appear in a cell in the fifth sheet.

Can anyone tell me how to do this? This is a simple example so if someone could show me how to do this I would be able to tailor it to my (much larger) sheets of stuff.

Any help, as always, will be very gratefully received.

Thanks in advance,
Chris.

=MAX(Sheet2:Sheet5!B:B)

which determines the highest number among the numbers in column B of each sheet from Sheet2 to Sheet5.
 

Forum statistics

Threads
1,148,195
Messages
5,745,285
Members
423,942
Latest member
excelhelp1423

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top