+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting with INDEX/MATCH functions on seperate worksheets.

  1. #1
    Registered User
    Join Date
    03-03-2009
    Location
    Gainesville, FL
    MS-Off Ver
    Excel 2002 SP3
    Posts
    39

    Conditional Formatting with INDEX/MATCH functions on seperate worksheets.

    I know there are many posts concerning this, but after scouring, I couldn't find one that fit my situation. I have a total of six worksheets, I am only concerned with two worksheets.

    Worksheet (functions!)
    This one has a list of numbers formatted as general. (Column G)
    Is actually a formula/macro that outputs a number... (didn't know if this mattered?)

    Please Login or Register  to view this content.
    Worksheet (ACD!)
    This one has a list of numbers formatted as text. (Column A)
    Actual typed numbers.

    I want to conditionally format the cells in (functions!$G) to turn green if it finds a match in (ACD!$A), and turn red if it doesn't find a match.

    Thanks in advance for the help.
    Last edited by giallofever; 04-17-2009 at 11:39 AM. Reason: Clarification
    Follow the rules:
    FORUM RULES
    Do not cross post:
    CROSS POSTING

    Everybody is ignorant, only on different subjects.
    From the great Will Rogers, the cowboy philosopher.

    Alcohol gives you infinite patience for stupidity.
    Sammy Davis, Jr.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Conditional Formatting with INDEX/MATCH functions on seperate worksheets.

    Post up your sheet so we don't have to fiddle around
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Conditional Formatting with INDEX/MATCH functions on seperate worksheets.

    yep
    =IF(ISERROR(SEARCH(".",F192)),CleanAll(F192),CleanAll(LEFT(F192,FIND(".",F192)-1)))
    doesnt mention sheet names!!!! and what is cleanall? is that some sort of udf?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    03-03-2009
    Location
    Gainesville, FL
    MS-Off Ver
    Excel 2002 SP3
    Posts
    39

    Re: Conditional Formatting with INDEX/MATCH functions on seperate worksheets.

    Sorry it took so long, it was a 7MB .xls file. Had to trim it to less than 1MB for upload. There is about 5000 more items in the spreadsheet, but I want to focus on this company because they are able to send their active products in .xls.

    CleanAll is a Module.

    Don't worry about the #REF in price field.

    Thanks guys, this has me stumped. And if you have any hints about better formulas, VBA, macros or the overall setup, please let me know.

    Good seeing you JBeaucaire, thanks for your help in the past. You truly are an asset to the community.

    Sorry guys, the Excel Help Forum DBase won't let me upload. Maybe later.

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Conditional Formatting with INDEX/MATCH functions on seperate worksheets.

    just for info . you can get conditional format to work by using a named range
    say you name a range on sheet2 myrange (say a1:a100)
    you can use on sheet 1 as a conditional format
    =match(x1,myrange,0) and choose a format now if x1 sheet1 matches in sheet2 a1:a100 format will kick in on the cell its applied to

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Conditional Formatting with INDEX/MATCH functions on seperate worksheets.

    Try zipping the file, too.

  7. #7
    Registered User
    Join Date
    03-03-2009
    Location
    Gainesville, FL
    MS-Off Ver
    Excel 2002 SP3
    Posts
    39

    Re: Conditional Formatting with INDEX/MATCH functions on seperate worksheets.

    Hey,

    Still wouldn't let me upload after zipping it. But I did find a solution that works for me for now.

    I named the assigned the range ACD!$A:$A to "ACDItem"

    I then used conditional formatting and set it to 'Formula is' and entered

    =COUNTIF(ACDItem,G192)

    Thanks.

+ 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