+ Reply to Thread
Results 1 to 3 of 3

Look up a value from a table, adding the text and changing the cell background colour

  1. #1
    Registered User
    Join Date
    01-11-2011
    Location
    Dorset
    MS-Off Ver
    Excel 2003
    Posts
    19

    Look up a value from a table, adding the text and changing the cell background colour

    Hi

    I am doing a Risk Register. I have one worksheet (Sheet 1) that contains the Risk Scoring Guide, on the second worksheet I have created the Risk Register "Generic Company Risks".

    If you go to Column H, you can select the Propbability (e.g Rare, unlikely, Moderate), which matches the table in sheet one.

    If you go to Column I, you can select the Impact (e.g. Insignificant, Minor, Significant....) which also matches the table in sheet one.

    What I am then trying to do in Column 'J" which is "total Risk score" is for it to automatically provide the score and colour scheme from worksheet 1.

    So say if I select in Column H "Moderate 3" and then select "Major 4", I would then expect Column J to automatically look up the table in worksheet 1 with these two values and then automatically change the cell accordingly to match, so the Cell for this example would be highlighted in yellow and would say "12 High" and the background of that cell would turn to yellow. So each time you change the options in columns H and I, column J will change accordingly and automatically to match the table in worksheet 1.

    I would really appreciate someones help here, I don't know whether a lookup table would work or whether I need to use "If' statements or a combination of both?
    Attached Files Attached Files

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Look up a value from a table, adding the text and changing the cell background colour

    Hello,

    you can use this formula, starting in cell J3 and copied down

    =INDEX('Risk Scoring Guide'!$C$5:$G$9,MATCH('Generic Company Risks'!H3,'Risk Scoring Guide'!$B$5:$B$9,0),MATCH('Generic Company Risks'!I3,'Risk Scoring Guide'!$C$4:$G$4,0))

    Then set up conditional formatting rules for each of the score values. You can set up conditions that combine several values, for example set up a conditional format with a formula like this for all dark blue.

    =OR(J2="Medium 1",J3="Medium 2",J2="Very low 2")
    Last edited by teylyn; 04-11-2016 at 09:10 PM.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,532

    Re: Look up a value from a table, adding the text and changing the cell background colour

    You can put this formula in J3:

    =IF(OR(H3="",I3=""),"",INDEX('Risk Scoring Guide'!$C$5:$G$9,MATCH(H3,'Risk Scoring Guide'!$B$5:$B$9,0),MATCH(I3,'Risk Scoring Guide'!$C$4:$G$4,0)))

    If you want the colours to change, you will need to use Conditional Formatting with about 9 different rules to cover all colours. Copy the formula down as required.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 01-05-2016, 06:36 PM
  2. [SOLVED] changing cell A1 BACKGROUND colour IF CELL A3 for example contains text
    By robbyweston in forum Excel General
    Replies: 5
    Last Post: 03-05-2012, 12:55 PM
  3. Changing background colour depending on value
    By rahulpandita in forum Excel General
    Replies: 2
    Last Post: 12-19-2010, 12:19 AM
  4. [SOLVED] Change Row Background Colour When Text Is Entered Into A Cell
    By AlexRoberts in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-20-2010, 01:28 PM
  5. Changing background colour transparency depending on cell value
    By wopoq in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-23-2010, 08:39 PM
  6. Match each cell in a columns background/text colour to that of another cell?
    By Wabby in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-12-2010, 05:41 AM
  7. Changing Background Colour Based on Cell Contents
    By Sukh in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-12-2006, 09:00 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1