• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup and show row id in matrix

igorsusa

Member
Hello,

I need help developing formulae for my matrix table.

In attached file you will find sheet "open risks" and "closed risks", look for columns "Impact" and "probability". In these two sheets rows will always be adding additionally, when we need to open new "risk" or new row.

I want to show the risk_id (column A in open and closed risks sheet) in the "matrix" sheet in the right bracket, so depending of the value that each row has in columns impact and probability. Only difference is, if the row is in "close sheet" then have risk_id wirtten with (closed) at the end, for example R-0029 (closed).

The values in matrix are just for presentation for you to get the idea. If the bracket is full, do not add it to the list, so don't worry about that.

Any help will be greatly appriciated!

Thank you.
 

Attachments

  • xxx test_2.xlsm
    64.8 KB · Views: 7
What about selecting Column A2:A100 in the closed worksheet

Apply a Custom Number format
Ctrl 1
Custom
@ "Closed"
 
hello,

I dont think I follow. I want risk id (column a in open and closed risks) written in the sheet matrix, under the right bracket. so if columns H and I in open and closed sheets are for example "1-insignificant" and "2-Seldom", then this rowid must be written under the bracket "Insignificant/Seldom" in matrix sheet. If the row is in open sheet, then it is without (closed), if it is in close sheetm than add (closed) at the end (see matrix sheet for example).

Hope it is clearer, thank you.
 
Hi Igor,

Just a question: is there any priority set that which risks should reflect first i.e. open risk or closed risks. The problem is you have given a space of say 8 risks to appear for any combinations, suppose you have 10 risk id on open and 5 on closed risk which risks should appear First?

Regards,
 
Hey SM,

There is no priority for open or close, you can put it in any order you want.


If it is easy for you to put the “open” first – so do it.


If there are more than 8 , just do not add them.

Igor
 
Hello Igor,

Changed your table names to tblOpen & tblClosed. Used one helper column to avoid repeating formulas. If you don't like to use helper, copy the formula in C3 and replace wherever you see $C3 in the formula.

Please see the attached. First 'Open' ID then 'Closed' IDs.

Hope this helps.
 

Attachments

  • Test2.xlsm
    64.7 KB · Views: 8
Hi Igor,

Although @Haseeb A had already gave you a solution, I would recommend you to convince your team to have two such MATRIX. One for open risks and one for closed. Anyhow one risk can take only one position. Combining them in one MATRIX will complicate the issue where no. of risks in any one will exceed 8. In that case all open will come but no Closed one will appear.

Just a suggestion.

Regards,
 
Hey friends, especially @Haseeb A, @Somendra Misra and @NARAYANK991.

OFFTOPIC: Narayank and SM, we got code to work, the ID's had to start with 0 for code to work appropriate :)

ONTOPIC: No need for making it two matrix sheets. I only need few corrections.

Formula in matrix is working OK, but it has some issue, I have (closed) written in every bracket- problem occured when I transfer them back from close to open sheet. Could you please check the attached, this is most important problem I have.

Second is to make columns impact, probability, risk type and Stategy for avoidance as mandatory in Open sheet. if these cells are empty then you cannot do anything, you show an message asking the user to add this fields

Third and not so important is is to add one more button in Open sheet to delete all lines from both sheets, (empty the matrix) and set the "ref" to 1.

Thank you very much!
 

Attachments

  • V5.xlsm
    65.5 KB · Views: 2
Hello!

Leave the coding, it is done. I just need help with the formuale in MATRIX sheet.

Formula in matrix is working OK, but it has some issue, there is (closed) written in every bracket- problem occured when I transfer them back from close to open sheet. Could you please check the attached, sheet MATRIX.

Please see attached file in previous comment.

Thank you.
 
My bad Igor...!

The problem was, left IF's 2nd parameter as default in SMALL. So IF is taking FALSE as value. When nothing data is there, calculating 0 as first value & INDEX is returning first value with (Closed).

Try this updated version. Red highlighted is the change. Used "" in IF for FALSE conditions.

=IFERROR(IFERROR(INDEX(Table1[Risk-ID],SMALL(IF(ISNUMBER(SEARCH(D$2,Table1[Impact]))*(MID(Table1[Probability],3,15)=LOOKUP("zzzzz",$B$3:$B3)),ROW(Table1[Risk-ID])-ROW(Table1[#Headers]),""),$C3)),INDEX(Table2[Risk-ID],SMALL(IF(ISNUMBER(SEARCH(D$2,Table2[Impact]))*(MID(Table2[Probability],3,15)=LOOKUP("zzzzz",$B$3:$B3)),ROW(Table2[Risk-ID])-ROW(Table2[#Headers]),""),$C3-SUMPRODUCT((MID(Table1[Probability],3,15)=LOOKUP("zzzzz",$B$3:$B3))*ISNUMBER(SEARCH(D$2,Table1[Impact])))))&" (Closed)"),"")
 
Hey everybody,

Is there any way I could accomplish this:

Double click in protected cell in matrix sheet e.i. "R-0001" or "R-0002 (Closed)" and after double click I am transformed to the source- so whether to closed or open sheet to that specific line (+ that line it is highlighted)?

I tried options -> advanced -> and unchecking the "allow editing directly in cells", but it's not working properly. I want this feature only if it can be done without unprotecting the cells in matrix sheet.

Is it possible?

Thanks.

Igor.
 

Attachments

  • RISK_PHASE_3_5.xlsm
    513.5 KB · Views: 3
Hi Igor,

As you have macro enabled file this is quite possible.

Paste this code in matrix sheet.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim bRiskFound As Boolean
  Dim sRiskID As String
  Dim sht As Worksheet
  Dim iHighlightRw As Integer
  Dim Rng As Range
 
 
  If Intersect(Target, Range("D3:H42")) Is Nothing Or Target.Cells.Count > 1 Then
  Exit Sub
  End If
 
  'If Then Exit Sub
 
  sRiskID = VBA.Trim(VBA.Replace(Target.Value, "(Closed)", "", , , vbTextCompare))
  sRiskID = VBA.Trim(VBA.Replace(sRiskID, "(Open)", "", , , vbTextCompare))
 
  If VBA.Len(sRiskID) = 0 Then Exit Sub
 
  Set Rng = Sheet2.Range("A4:A" & Sheet2.Range("A65000").End(xlUp).Row)
 
  For Each cell In Rng
  If cell.Value = sRiskID Then
  Set sht = Sheet2
  iHighlightRw = cell.Row
  bRiskFound = True
  Exit For
  End If
  Next
 
  If Not bRiskFound Then
  Set Rng = Sheet3.Range("A4:A" & Sheet2.Range("A65000").End(xlUp).Row)
 
  For Each cell In Rng
  If cell.Value = sRiskID Then
  Set sht = Sheet3
  iHighlightRw = cell.Row
  bRiskFound = True
  Exit For
  End If
  Next
 
  End If
 
  If bRiskFound Then
  sht.Activate
  ActiveSheet.Range("A" & iHighlightRw & ":R" & iHighlightRw).Select
 
 
  End If
End Sub

This will meet your requirements I believe.

Regards,
Prasad DN
 
Back
Top