MAX Value of a CELL in ALL sheets in a workbook

[Corey]

Is there a way to display in sheet1 the MAX value of a cell through ALL
sheets in that workbook ?

Say [T55].
I want this cell to display the MAX value in all sheets [J55].

I want to use this as a quote number, but not every sheet will have a value.
I tried a MAX formula, but i need to know the 1st and Last sheet names
before hand.

Regards

Corey....

 

[Ken Johnson]

Is there a way to display in sheet1 the MAX value of a cell through ALL
sheets in that workbook ?

Say [T55].
I want this cell to display the MAX value in all sheets [J55].

I want to use this as a quote number, but not every sheet will have a value.
I tried a MAX formula, but i need to know the 1st and Last sheet names
before hand.

Regards

Corey....

Hi Corey,

Have you looked at the following topic in Help?...

"Refer to the same cell or range on multiple sheets"

It seems to me that this is what you are trying to do.

Ken Johnson

 

[Corey]

Ken,
Thanks for the reply.

I will see if i can work through some of the help files....

Corey....

Is there a way to display in sheet1 the MAX value of a cell through ALL
sheets in that workbook ?

Say [T55].
I want this cell to display the MAX value in all sheets [J55].

I want to use this as a quote number, but not every sheet will have a
value.
I tried a MAX formula, but i need to know the 1st and Last sheet names
before hand.

Regards

Corey....

Hi Corey,

Have you looked at the following topic in Help?...

"Refer to the same cell or range on multiple sheets"

It seems to me that this is what you are trying to do.

Ken Johnson

 

[Corey]

Code i recorded below:

I ned to adapt this to look to ALL sheets.
The first sheet will always be "Template", BUT the LAST Sheet will change
all the time with NEW sheets added daily.
Therefore is there a LAST.SHEET code instead of "Template (4)" in this
example, SEE comments below in code.

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 26/06/2006 by Corey

'
Range("L53").Select
ActiveCell.FormulaR1C1 = "=MAX('Template:Template (4)'!R[-8]C[-10])" '
<========= Want the [Template (4)] to be a simple [LAST.SHEET] statement,
without refering to sheet BY name.
Range("L54").Select
End Sub

Can it be done ?

Corey....

 

[Ken Johnson]

Hi Corey,

Try this...

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 26/06/2006 by Corey

Dim WbCount As Integer
WbCount = ActiveWorkbook.Worksheets.Count
Range("L53").Select
ActiveCell.FormulaR1C1 = "=MAX('Template:Template (" _
& WbCount & ")'!R[-8]C[-10])"
Range("L54").Select
End Sub

If your workbook has 10 sheets then WbCount will equal 10, so if your
last sheet is named Template(10) that should give the correct formula.

When I tried this line of code on a standard workbook with 3 sheets...
Sheet1, Sheet2, and Sheet3

ActiveCell.FormulaR1C1 = "=MAX('Sheet1:Sheet" & WbCount &
"'!R[-8]C[-10])"

I ended up with this formula in L53...

=MAX(Sheet1:Sheet3!B45)

Hope this is useful.

Ken Johnson

 

[Corey]

Thanks for the reply Ken.
The problem is the sheet name will NOT be Template (?).
Each sheet will be given a number value, and there will be no definite value
that could be calculated.
I was hoping there is a maybe, COUNT.LAST.SHEET. Get Sheet Name code??

Corey....

 

[Ken Johnson]

Hi Corey,

Try this...

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 26/06/2006 by Corey

Range("L53").Select
ActiveCell.FormulaR1C1 = "=MAX('Template:" & _
Worksheets(Worksheets.Count).Name & _
"'!R[-8]C[-10])"
Range("L54").Select
End Sub

It worked with Sheet1 named Template and any number of other sheets
regardless of their name. The resulting formula in L53 returns the
maximum of the B45 cell values on all of the sheets.

Hope it works for you too.

Ken Johnson