Solved Display worksheet name based on a condition

August 10, 2012 at 04:27:13
Specs: Windows 7
I have an excel sheet with worksheets say A, B, C,D representing each of the sales unit. each of these sheets has rows of different sales risk say 1, 2,3,4 with a score associated with each risks

Another worksheet E has a column 'max risk' which fetches the max score of each risk from each of the worksheet A,B,C and D

Now i want to add another column in worksheet E, say 'Dept' which displays the worksheet name from which the max score has come from. Am unable to do this.

Example (worksheet E):

Risk No. Risk Desp Max Risk Score DeptĀ 
1 Text 16 A
2 Text 10 B,C (In case same score available in both the sheets)
3 Text 8 C

How can i do this?Urgently need help. Thank you.

See More: Display worksheet name based on a condition

Report •

August 12, 2012 at 18:59:24
✔ Best Answer
First, a posting tip:

Before posting example data in the forum, please click on the blue line at the bottom of this post and read the instructions found via that link.

It's hard for us tell which columns your data is in.

As for your question, you can't do what you want with any Excel built in function. You will need to use VBA to create a User Defined Function (UDF) such as the one below.

After pasting this code into a Standard module in the VBA editor, you can enter =SheetMax(Cell Reference) in a cell and it should return the sheet names where the MAX values are found.

The Cell Reference should be the cell in which your MAX value is returned.

The code assumes that your "Risk" values are in Column B of each sheet. Modify the code as required.

Function SheetMax(ByVal Target As Range)
  For shts = 1 To Sheets.Count
     lastRw = Sheets(shts).Range("B" & Rows.Count).End(xlUp).Row
       For Each cell In Sheets(shts).Range("B1:B" & lastRw)
         If cell = Target.Value Then
            tmpSheetMax = tmpSheetMax & Sheets(shts).Name & ", "
            Exit For
         End If
   SheetMax = Left(tmpSheetMax, Len(tmpSheetMax) - 2)
End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question