How do i automatically get a column of a table filled up after filling up the values in the others which it references?

Hi Microsoft community, I currently have a task at hand and I'm not too sure how to solve it.

I would like to automate the rightmost column on the RISK Ratings based on Likelihood and Impact.

For example, under Risk 1, if i were to click "rare" as the likelihood and "insignificant" as the impact both from their own dropdown lists, i would like the risk rating to be automatically filled as "Low" with reference to the colourful table above.

Another example would be if i chose Rare as Likelihood and Catastrophic as the Impact, i would automatically get back the risk rating of Medium on the rightmost column.

Thank you so so much to whoever may have the answer to help me with this task.. <3


Cheers
Paul Edstein
(Fmr MS MVP - Word)

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Here is another approach.

In each of the Severity and Likelihood cells, there is a Dropdown Content Control containing the numbers from 1 to 5 (1 being the lowest risk and 5 the greatest) from which the user selects the appropriate value.  Then in the fourth column and the sixth column, there is a field that computes the product of the values selected in the two cells to the left.

The calculation does not however occur automatically, rather it is performed by the running of a macro that contains the following code, which in addition to performing that calculation, also applies the relevant colour to each of the cells in the second through seventh columns of the table.

This is the code in the macro, which is run from a button on a custom tab on the ribbon

Dim i As Long, j As Long, k As Long, t As Long

Dim acell As Cell

Dim crange As Range

Dim timestart As Date

Dim timeend As Date

Dim elapsedtime As Long

Sub ColorizeAll()

timestart = Now

On Error GoTo ExitThis

With ActiveDocument

    For t = 2 To .Tables.Count Step 2

        With .Tables(t)

            For i = 1 To .Rows.Count - 1

                If Len(.Cell(i, 1).Range.Text) > 2 Then

                    j = i

                Else

                    Exit For

                End If

            Next i

            For i = 1 To j

                With .Rows(i)

                    .Cells(4).Range.Fields.Update

                    .Cells(7).Range.Fields.Update

                    For k = 2 To 7

                        Set crange = .Cells(k).Range

                        crange.End = crange.End - 1

                        Select Case Val(crange.Text)

                            Case 1 To 4

                                crange.Cells(1).Shading.BackgroundPatternColor = RGB(153, 204, 0)

                            Case 5 To 12

                                crange.Cells(1).Shading.BackgroundPatternColor = RGB(255, 153, 0)

                            Case Is > 12

                                crange.Cells(1).Shading.BackgroundPatternColor = RGB(255, 80, 80)

                            Case Else

                                If Len(crange.Text) < 5 Then

                                    crange.Cells(1).Shading.BackgroundPatternColor = RGB(255, 255, 255)

                                End If

                        End Select

                    Next k

                End With

            Next i

        End With

    Next t

End With

This system is used in a document that contains Risk Assessments for something like 100 activities and situations.  If you only had a couple of Risk Assessments to deal with, it would be feasible for the calculation and coloring of the cells to be performed by the use of the Document Content Control On Exit event.

Hope this helps,
Doug Robbins - MVP Office Apps & Services (Word)
dougrobbinsmvp@gmail.com
Screen shots by Snagit from www.techsmith.com

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

 
 

Question Info


Last updated June 29, 2020 Views 16 Applies to: