# Using MAX (or similar) over multiple worksheets

#### cidgreen

##### New Member
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.

Chris.

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### Aaron

##### Board Regular
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
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.

##### MrExcel MVP
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.

Chris.

=MAX(Sheet2:Sheet5!B:B)

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

Replies
3
Views
230
Replies
5
Views
128
Replies
3
Views
262
Replies
8
Views
334
Replies
0
Views
133

1,147,748
Messages
5,742,976
Members
423,769
Latest member
LongToast

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

### Which adblocker are you using?

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

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