VBA/Excel question about displaying the name of the workshee

Below is a simple workbook that will help illustrate what I need help with.

Basically in the Q1 sheet, I have a min and a max feild. What I would like to do is have Excel automatically display the nameof the worksheet that has the MAX and MIN values. There is a comment in Q1 that gives a example. I know there are a few ways to do this, however I would like to beable to just have the worksheet name get displayed.

http://www3.sympatico.ca/daniel.demers2/Book1.xls

Daniel

Heya, this is Zenaph (Matt in RL). Try typing this in:

=IF(Jan!C3

heh, thanks for trying bro . However I need the "displayed month" to come from the worksheet name itself.

Example: if the lowest month came from the first sheet, "Jan" would be displayed, if I were to change the name on the worksheet to Oct, it would then display "Oct"...ect

I was afraid my explanation was not clear enough. I hope this is more clear.
I dont think its possible to do it in Excel, without messing around in VBA (where I just happen to be clueless...).

Do yourself a favor, list the cell (range in vba) that your comparison data can be found. The answer is around the corner........

Cheers!

[robfo0]

Here is a quick bit of code. I don't know if its the most efficient, theres probably some shortcut i dont know about, but here goes anyway

Sub SheetProfit()

Dim Profit As Long
Dim ProfitSheetName As String
Dim Min As Long
Dim MinSheet As String
Dim Max As Long
Dim MaxSheet As String

Application.ScreenUpdating = False

'Define Min as C3 on the first worksheet
Min = Worksheets(1).Range("C3")
MinSheet = Worksheets(1).Name

'Define Max as C3 on the first worksheet
Max = Worksheets(1).Range("C3")
MaxSheet = Worksheets(1).Name

'Loop through the worksheets
For Each xls In ActiveWorkbook.Worksheets
xls.Activate

'Stops the For loop when the loop gets to Sheet Q1
If ActiveSheet.Name = "Q1" Then
Exit For

Else

'Sets Profit equal to C3
Profit = Range("C3")
ProfitSheetName = ActiveSheet.Name

'If Min is greater than Profit in this sheet, redefine Min and catch the sheet name
If Min > Profit Then
Min = Profit
MinSheet = ProfitSheetName
End If

'If Max is less than Profit in this sheet, redefine Max and catch the sheet name
If Max < Profit Then
Max = Profit
MaxSheet = ProfitSheetName
End If
End If

'Go to the next sheet
Next xls

'Puts the values of Min and Max and the corresponding sheet names into their places in Q1
Worksheets("Q1").Range("C6") = Min
Worksheets("Q1").Range("D6") = MinSheet

Worksheets("Q1").Range("C7") = Max
Worksheets("Q1").Range("D7") = MaxSheet

Application.ScreenUpdating = True

End Sub


A few notes about the code. Sheet Q1 should be the last sheet in the workbook or it wont check the worksheets after that. Also, if 2 worksheets with a min or max value, the code will take only the first. you can change this if you want by making some condition or something if Max or Min = Profit. Hope this helps.

[ This Message was edited by: robfo0 on 2002-02-26 22:18 ]

works nicely

Thanks,
Daniel

Some alternative code (doesn't need sheet Q1 to be the last sheet :-

Dim ws As Worksheet, min As Range, max As Range, x%, y%
With Worksheets("Q1")
Set min = .[C6]
Set max = .[C7]
For Each ws In Worksheets
If ws.Name <> "Q1" Then
If x = 0 Then
If ws.[c3].Value = min Then
.[D6].Value = ws.Name
x = 1
End If
ElseIf y = 0 Then
If ws.[c3].Value = max Then
.[D7].Value = ws.Name
y = 1
End If
Else: Exit For
End If
End If
Next
End With