+ Reply to Thread
Results 1 to 8 of 8

Validating Multiple Ranges

  1. #1
    Registered User
    Join Date
    05-17-2011
    Location
    Adelaide, South Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Validating Multiple Ranges

    Hi everyone

    I have four non adjacent cells A1, A3, C1 and C3. Each cell contains a risk value of either "low", "medium", "high" or "extreme".

    I'm looking for some help with efficient code (Excel 2007) to validate the values in these cells, and record the highest level of risk in a variable called say "MaxRisk".

    I can achieve it with a bunch of messy IF tests (I have multiple series of these four cell clusters) but can't help thinking there has to be a smarter way.

    Hope someone can help.

    Kind regards,

    HappyHole
    Last edited by HappyHole; 09-14-2011 at 07:05 PM. Reason: Solved

  2. #2
    Valued Forum Contributor MaczaQ's Avatar
    Join Date
    06-03-2011
    Location
    Poland
    MS-Off Ver
    Excel 2003 / XP
    Posts
    510

    Re: Validating Multiple Ranges

    if it is possible please attache your workbook with dummy data.
    please explain if any of cells(a1,a3,c1,c3) could be black or the same as each other.
    Recording highest level of risk thats mean only value (low, medium, high or extreme) without info which cell have it ?

    For now your task is quite difficult to understand how it should works

    Best Regards
    MaczaQ

  3. #3
    Registered User
    Join Date
    05-17-2011
    Location
    Adelaide, South Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Validating Multiple Ranges

    Hello

    if it is possible please attache your workbook with dummy data.
    please explain if any of cells(a1,a3,c1,c3) could be black or the same as each other.
    Recording highest level of risk thats mean only value (low, medium, high or extreme) without info which cell have it ?

    Here is an example spreadsheet. Hopefully this helps to explain. I need to evaluate each series of four cells and record the highest value (which I have manually added) as a varaible.

    Thanks

    Happy Hole
    Attached Files Attached Files

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,593

    Re: Validating Multiple Ranges

    Hi,

    Does the attached help

    Regards
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    05-17-2011
    Location
    Adelaide, South Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Validating Multiple Ranges

    Okay so here is what I have. It works, but as I said doesn't look efficient:

    Please Login or Register  to view this content.
    Hope someone more advanced than I am can assist. This is all part of a form for employees to fill out that is becoming increasingly more complex, so perhaps I'm being overly sensitive about how big all this code is getting ....

    Thanks.

    Happy Hole

  6. #6
    Registered User
    Join Date
    05-17-2011
    Location
    Adelaide, South Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Validating Multiple Ranges

    Does the attached help
    Hi Richard

    Thanks - I might be able to work with it if I can configure the numbers. Currently 4 x medium looks like it evaluates to "Extreme".
    Hoping to get a VB version because the stored variable is then used elsewhere.

    Thanks.

    Happy Hole

  7. #7
    Valued Forum Contributor MaczaQ's Avatar
    Join Date
    06-03-2011
    Location
    Poland
    MS-Off Ver
    Excel 2003 / XP
    Posts
    510

    Re: Validating Multiple Ranges

    try this function
    Please Login or Register  to view this content.
    Best Regards
    MaczaQ
    Last edited by MaczaQ; 09-14-2011 at 04:25 AM.

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,593

    Re: Validating Multiple Ranges

    Hi,

    Sorry, my error.

    Use the following 4 values in the small lookup table instead
    1,9,37,149

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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