+ Reply to Thread
Results 1 to 9 of 9

Conditional formatting with multiple rules (vlookup and if statment)

  1. #1
    Registered User
    Join Date
    07-02-2016
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    5

    Conditional formatting with multiple rules (vlookup and if statment)

    Hi All,

    I'm looking to apply conditional formatting to a list of cells so that they change colour if that same value occurs on a separate sheet. This is easily done with a vookup in the conditional formatting field.
    The complication is that I want to apply an additional condition that says that the formatting should only occur if, in addition to the cell value cropping up on the separate sheet, a separate column in the initial sheet is also equal to a predefined value.

    I've been trying to use the following formula in conditiona formatting to no avail.
    Here the 'High estimators' is the second sheet, and C10 is the adjacent cell.

    =AND((VLOOKUP(N11,'High estimators'!$G$11:$G$15,1,FALSE)),(C10="RF"))

    Can anyone suggest a modification to make this work? or perhaps an alternative approach completely. And secondly, if you can crack this, can you advise how to apply this same formula to every cell in my list (the complication here is that, the N11 will need to increase by 1 each time, as will C10)

    Thanks!
    Jack

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Conditional formatting with multiple rules (vlookup and if statment)

    Maybe this, but I would need your file to test it:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    As for the second part, what does this mean? Are you referring to row numbers, or values in the cells?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: Conditional formatting with multiple rules (vlookup and if statment)

    As far as I can see (and test) it should work fine. You've got some unnecessary brackets, but they don't affect the calculation.

    What exactly isn't working? Can you upload a file showing what's going wrong? (Remove any confidential information first and annotate with text or comments what's working/not working and what you'd like to happen.)

    Regarding your last point, if you have a range of cells then you can apply the CF to all of them just by dragging down from N11. The CF will auto-update to use N12, N13, etc - and also C11, C12, etc - because they are relative references.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  4. #4
    Registered User
    Join Date
    07-02-2016
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    5

    Re: Conditional formatting with multiple rules (vlookup and if statment)

    Thank you for your responses. The formula you've provided is returning an error - I've not been able to determine what the problem it. How do you attach a spreadsheet? I will upload A spreadsheet to test on in a moment.

    @ Aardigspook, regarding my last point, when you put a formula into conditional formatting it only works for the selected cell. If you want to apply that condition to multiple cells (say a whole column), there are two ways I see you can do it, the first is to use the format painter, the second is to change the "Applied to" field in the conditional formatting rules manager to cover the range of cells you want to apply the conditional format rules too. However in both these cases it will apply the exact same formula to the range of cells. It won't auto update the relative references like it would if you put the formula directly into a cell and dragged it down. Also, you can't drag down a conditional format as far as I'm aware? That would simply drag the contents of the cell down (not the conditional format rules). My issue is, even if I can sort out the correct formula to use in that cell, I need a way to drag it down like you say through over 3000 rows and have those relative references update to the corresponding cell. It doesn't seem to do this?

    Thanks again!

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Conditional formatting with multiple rules (vlookup and if statment)

    Quote Originally Posted by jrsangster View Post
    However in both these cases it will apply the exact same formula to the range of cells. It won't auto update the relative references like it would if you put the formula directly into a cell and dragged it down.
    Sorry, this is wrong.

    If you use your original CF formula for N11 and expand the applicable range to N11:N100 then when Excel evaluates conditional formatting for cell N100 it will effectively apply the formula


    =AND((VLOOKUP(N100,'High estimators'!$G$11:$G$15,1,FALSE)),(C99="RF"))

    because those are relative references. The confusing thing is that you will never see this version of the formula anywhere, not even if you select cell N11 then look at the CF for that selected cell. You will still see the original formula. That is the one confusing thing about the "new" CF (introduced in Excel 2007).

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Conditional formatting with multiple rules (vlookup and if statment)

    To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: Conditional formatting with multiple rules (vlookup and if statment)

    Quote Originally Posted by jrsangster View Post
    you can't drag down a conditional format as far as I'm aware?
    If you drag down a cell it will copy everything, including the formatting (including Conditional Formatting) and formulas (dependent on relative or absolute references). It's only if you right-click and choose 'Fill without formatting' that it won't.

    6StringJazzer has explained the issue with CF appearing to show the wrong references, and how to attach a file. One of us will have a further look once you've had a chance to do that.

  8. #8
    Registered User
    Join Date
    07-02-2016
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    5

    Re: Conditional formatting with multiple rules (vlookup and if statment)

    Thanks StringJazzer/Aardigspook, apologies - I can see now that you're correct, I was thrown off because, like you said the formula doesn't update when you view it. I've tested it now for my own peace of mind and I'm happy that it does update behind the scenes which solves that issue

    Also, StringJazzer I had a tinker with the formula you suggested, it now works on my spreadsheet. The error it was returning just related to a missing bracket. I've copied the final formula I've used.

    =AND((NOT(ISERROR(MATCH(N10,'High estimators'!$G$11:$G$15,0)))),(C10="RF"))

    Thanks very much for your help!

  9. #9
    Registered User
    Join Date
    07-02-2016
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    5

    Re: Conditional formatting with multiple rules (vlookup and if statment)

    It wouldn't let me upload a spreadsheet to test on, but I've uploaded a couple of screenshots of how the tables work, with your formula inserted. You can see that it works perfectly now. Thanks for your help, much appreaciated

    Sheet1.PNG
    Sheet2.PNG

+ 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. [SOLVED] Changing Conditional Formatting Rules Based on Vlookup?
    By neato in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-04-2016, 02:21 PM
  2. [SOLVED] Using if and And together in conditional formatting multiple rules!
    By Katieloulouise in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-03-2015, 01:00 PM
  3. Conditional Formatting, multiple rules
    By jsch08 in forum Excel General
    Replies: 1
    Last Post: 04-30-2013, 11:30 PM
  4. Conditional Formatting with multiple rules
    By carrod65 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-24-2012, 03:43 PM
  5. Conditional Formatting with multiple rules
    By Peter Richardson in forum Excel General
    Replies: 6
    Last Post: 07-07-2011, 02:08 AM
  6. Conditional formatting multiple rules
    By candrew03 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-16-2011, 02:35 PM
  7. Conditional Formatting Multiple Rules
    By ems.payroll in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-07-2008, 12:31 PM

Tags for this Thread

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