+ Reply to Thread
Results 1 to 5 of 5

Changing Conditional Formatting Rules Based on Vlookup?

  1. #1
    Registered User
    Join Date
    06-06-2011
    Location
    UK
    MS-Off Ver
    2016
    Posts
    22

    Question Changing Conditional Formatting Rules Based on Vlookup?

    Hi,

    The title may be a little misleading, that is just my current train of thought.
    I'm trying to develop a spreadsheet that helps track inventory for a friend's property business.

    So we have a tab called Current Inventory that looks like this:

    Pots Pans Mugs Knives Forks
    123 Street 4 Bed Lux 1 2 3 4 5
    10 Great Street 3 Bed Lux 2 3 4 5 6
    1 Burton Place 1 Bed Apt 1 3 4 5 6
    22 Jump St 2 Bed Apt 4 3 2 1 5
    100 Another St 3 Bed Apt 1 2 3 4 5
    25 Main St 3 Bed Apt 25 2 3 4 5

    And another tab called Starting Inventory that is very similar but with different numbers

    Pots Pans Mugs Knives Forks
    4 Bed Lux 10 4 10 20 20
    3 Bed Lux 10 4 7 10 10
    1 Bed Apt 4 1 5 10 10
    2 Bed Apt 4 2 5 10 10
    3 Bed Apt 4 2 5 10 10

    We'd like conditional formatting on the current inventory tab that shows if the current inventory is higher, lower or exactly the same as the starting inventory for a house of it’s type.

    So for example, we’d would want to check 25 Main St against the inventory for a 3 Bed Apt and would like it to show Blue for mugs (there are too many), Green for pans (there are the right amount of pans) and Red for the rest, as items have been lost.

    Because we’re comparing it to a property of a certain type we can’t just use generic rules and IF statements in the rules quickly got messy as there are a lot of property types.

    I feel like it should be simple but I just can’t work it out and I’ve been looking at it for so long I think I’m missing the bigger picture.So I thought I'd ask and see if anyone had any ideas.
    If anyone knows how to do this or can point me in the right direction, I'd be hugely grateful.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,731

    Re: Changing Conditional Formatting Rules Based on Vlookup?

    possibly a countifs()

    lets work something up

    If you could upload a sample sheet that would save some time

    Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

    To attach a file to your post,
    click "Go advanced" (next to quick post),
    scroll down until you see "manage Attachments",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,731

    Re: Changing Conditional Formatting Rules Based on Vlookup?

    used index match - Vlookup would do to

    But this does assume that the heading are identical order on the two sheets

    see attached
    Attached Files Attached Files

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,731

    Re: Changing Conditional Formatting Rules Based on Vlookup?

    I have added a way to lookup the
    type of property and also the items
    so the order does not matter

    see attached

    Three rules setup - one for each colour

    =INDEX(Sheet2!$B$2:$F$6, MATCH($B2,Sheet2!$A$2:$A$6,0), MATCH(C$1,Sheet2!$B$1:$F$1,0))<C2
    Blue

    =INDEX(Sheet2!$B$2:$F$6, MATCH($B2,Sheet2!$A$2:$A$6,0), MATCH(C$1,Sheet2!$B$1:$F$1,0))=C2
    Green

    =INDEX(Sheet2!$B$2:$F$6, MATCH($B2,Sheet2!$A$2:$A$6,0), MATCH(C$1,Sheet2!$B$1:$F$1,0))>C2
    Red
    Attached Files Attached Files
    Last edited by etaf; 02-04-2016 at 01:50 PM.

  5. #5
    Registered User
    Join Date
    06-06-2011
    Location
    UK
    MS-Off Ver
    2016
    Posts
    22

    Re: Changing Conditional Formatting Rules Based on Vlookup?

    Ah - of course! Thank You, this makes sense. I'll go ahead and apply it to my spreadsheet.

    Thank you for your help

+ 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] Conditional Formatting - creating rules based on colours in cell list
    By milkychips in forum Excel General
    Replies: 5
    Last Post: 01-02-2016, 12:00 PM
  2. Conditional Formatting Rules from multiple columns based on conditions!!!
    By vijay4b7 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-08-2014, 05:22 PM
  3. conditional formatting rules for a cell on sheet 1 based on rules from sheet 2
    By jsard in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 08-15-2013, 09:22 AM
  4. Changing text color using vlookup: conditional formatting or VB?
    By kerryhaglund in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-07-2013, 08:01 PM
  5. 3+ Conditional Formatting Rules for cell range based on Formula from other cells
    By osborsm9 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-06-2013, 08:48 PM
  6. Conditional formatting based on changing dates?
    By Eroos in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-11-2011, 11:14 AM
  7. Conditional formatting based on multiple rules
    By Pyrex238 in forum Excel General
    Replies: 7
    Last Post: 05-06-2011, 03:20 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