Max value in same cell across ALL worksheets

[Corey]

What would be formula to look for the [Max] value in say cell "I12" ACROSS
ALL worksheets with a workbook ?

Currently if i use the MAX formula i get a result of the current worksheet
only.

Corey....

 

[Biff]

Hi!

=MAX(First:Last!I12)

Where First is the name of the first sheet tab and Last is the name of the
last sheet tab. Any sheets that are in between will be included in the
formula reference.

Biff

 

[Corey]

thanx
Is there not an ALL SHEETS formula?


Regards

Corey

Hi!

=MAX(First:Last!I12)

Where First is the name of the first sheet tab and Last is the name of the
last sheet tab. Any sheets that are in between will be included in the
formula reference.

Biff

What would be formula to look for the [Max] value in say cell "I12"
ACROSS ALL worksheets with a workbook ?

Currently if i use the MAX formula i get a result of the current
worksheet only.

Corey....

 

[Biff]

Is there not an ALL SHEETS formula?

That is an all sheets formula!

Maybe I didn't do a good job of explaining it!

Assume your sheet tabs are in this order:

Sheet1 | Sheet2 | Sheet3 | Sheet4

You want the MAX from cell I12 on ALL sheets.

=MAX(Sheet1:Sheet4!I12)

ALL sheets that are physically located in between Sheet1 and Sheet4
(inclusive) will be included in the formula.

Biff

thanx
Is there not an ALL SHEETS formula?


Regards

Corey

Hi!

=MAX(First:Last!I12)

Where First is the name of the first sheet tab and Last is the name of
the last sheet tab. Any sheets that are in between will be included in
the formula reference.

Biff

What would be formula to look for the [Max] value in say cell "I12"
ACROSS ALL worksheets with a workbook ?

Currently if i use the MAX formula i get a result of the current
worksheet only.

Corey....

 

[Corey]

When i place that in the formula box and hit enter, it opens up a file
dialog box?

Is that right ?

 

[Corey]

Ok, got it.

I have the tabs hiden, and need it like this, so unless i manually look for
the last created sheet, i do dnot know the last value to put in the formula?
COrey....

news:[email protected]...

 

[Biff]

When i place that in the formula box and hit enter, it opens up a file
dialog box?

Is that right ?

No!

I don't think I can explain it any better than I already have.

What is the name of the very first tab in your file?
What is the name of the very last tab in your file?

The formula is:

=MAX(name of the very first tab in your file:name of the very last tab in
your file!I12)

Biff

 

[Biff]

Another way to do this:

Create a new empty sheet named Start and make it the very first tab in the
file.
Create a new empty sheet named End and make it the very last tab in the
file.

Then:

=MAX(Start:End!I12)

Any new sheets you create and then hide need to be placed in between the
Start sheet and the End sheet.

If you don't want to do it this way then you need some VBA code. I can't
help with that.

Biff