Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: VBA/Excel question about displaying the name of the workshee

  1. #1
    Guest

    Default

    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

  2. #2
    Guest

    Default

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

    =IF(Jan!C3

  3. #3
    Guest

    Default

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

  4. #4
    Guest

    Default

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

    Cheers!

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Guest

    Default

    works nicely

    Thanks,
    Daniel

  7. #7
    Guest

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •