Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 18

Thread: Conditional Formatting Between Two Worksheets

  1. #1
    Join Date
    5th February 2007
    Posts
    33

    Conditional Formatting Between Two Worksheets

    I have 2 spreadsheets of names (~2500 and ~1800) and a bunch of corresponding data continuing down the row. both are structured this same way:

    Example:
    Row1: LAST, FIRST, data1, data2, data3, etc...

    How can I construct a formula so it will "check" the larger sheet for duplicate names (a row containing exactly the same FIRST and LAST names), & then change the cells' fill colors in the smaller sheet from white to red for all rows meeting this condition


    One other piece of information which might be important:
    For these rows containing duplicate first & last names, the entire row is not a duplicate entry; only the names will match (columns A & B)... While the other columns down the row will have different values between sheet1 & sheet2. Not sure if this changes anything.... Thanks
    Last edited by liiv17; February 18th, 2007 at 03:56.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,543

    Re: Formula: Change Fill Color If Row Contains Data Common With Another Sheet

    Hi Liiv - this is possible to do alright with Coniditonal Formatting I think... When you say an Exact match... do you really mean EXACT... for example

    does "John", "Smith" = "JOHN", "SMITH"

    Also - I presume these are two sheets in one workbook, right?

    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  3. #3
    Join Date
    5th February 2007
    Posts
    33

    Re: Formula: Change Fill Color If Row Contains Data Common With Another Sheet

    Quote Originally Posted by Ger Plante
    do you really mean EXACT... for example
    does "John", "Smith" = "JOHN", "SMITH"
    well, all names happen to already be formatted as "John" "Smith" (there is no variation in capitalization). So, let's operate under whichever assumption allows for the easiest formula

    Quote Originally Posted by Ger Plante
    these are two sheets in one workbook
    they weren't, but I can very easily just cut & paste them as sheets in the same workbook.... so, yes-- now they are two in a single workbook

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,543

    Re: Formula: Change Fill Color If Row Contains Data Common With Another Sheet

    Hmmm.... me an my big mouth

    Not so easy when I sat down to do this with Conditional Formatting.... sorry three more questions...

    1) by any chance are the columns sorted by Last Name / First Name? If not can they be...? (it would make it a lot easier if they were sorted).
    2) Do you absolutely require a Formaula for this? Would VBA do?
    3) Is it possible (are you allowed) to add an additional column to one of the sheets to help us along?

    Thanks Liiv.
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  5. #5
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Formula: Change Fill Color If Row Contains Data Common With Another Sheet

    Can you attach your workbook, or a part of it, showing the layout and the cells to be formatted please. I will use formulas.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,543

    Re: Formula: Change Fill Color If Row Contains Data Common With Another Sheet

    I'm sure the Gringe will nail this, but in the mean time, attached is a sample...

    On Sheet 1, I have used a helper Column where I just combined last name and first name.

    One Sheet 2, I have used conditional formatting. The condition on the formatting uses a vlookup of a combination of LastName and FirstName to lookup the helper column in Sheet 1. Not sure if this is helpful.

    Ger
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Ger Plante; February 18th, 2007 at 04:26.
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  7. #7
    Join Date
    5th February 2007
    Posts
    33

    Re: Formula: Change Fill Color If Row Contains Data Common With Another Sheet

    attached a sample work book trimmed down so both sheets only contain entries where last names begin with the letter J or K.

    both are sorted alphabetically by LAST then FIRST.

    My hope is for a formula in sheet1 to change the fill color so it stands out, IF the full-name in that entry is duplicated in sheet2.

    In my actual workbook, the data would extend over to column AF. It doesn't matter if the formula fills the entire row w/ a new color or just the first and last name.. I really just want to be able to quickly identify the duplicate entries

    thanks for the help
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    5th February 2007
    Posts
    33

    Re: Formula: Change Fill Color If Row Contains Data Common With Another Sheet

    Quote Originally Posted by Ger Plante
    On Sheet 1, I have used a helper Column where I just combined last name and first name.

    One Sheet 2, I have used conditional formatting. The condition on the formatting uses a vlookup of a combination of LastName and FirstName to lookup the helper column in Sheet 1. Not sure if this is helpful.
    it's definitely helpful, but i just dont know the excel language (vlookup for instance) to say as a formula:
    if sheet1 "First&Last" equals sheet2 "First&Last," then fill the sheet1 cell red for that row

    in fact-- you actually just taught me how to combine data in two cells (=A1&B1)... yeah im pretty much brand new
    Last edited by liiv17; February 18th, 2007 at 05:05.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,543

    Re: Formula: Change Fill Color If Row Contains Data Common With Another Sheet

    Find attached a spreadsheet as requested.

    Now for the explanation....

    Firstly, go to Sheet 2 and enter the this formula in Cell E3:
    =A3&B3
    This will concatenate the first name and last name.

    Selecting Sell E3, drag the formula down to the last row to fill all the rows. This should now have concatenated all rows with First Name and Last name.

    Highlight the Entire Column E with the mouse (it should go blue). When the Column is Selected enter "MY_NAMES" in the top left box (not the formula bar). "MY_NAMES" now refers to the entire column E and we will use this name shortly.

    On the first sheet, select cell A3 and click and drag to select all the cells down to D159.

    When all the cells are highlighted, go to Menu "Format" - "Conditional Formatting"... select "Condition1" as "Formula Is".

    In the box that appears copy paste this formula into it.
    =ISERROR(VLOOKUP($A3&$B3,MY_NAMES,1,FALSE))

    Set the formatting background to red.

    This is all done in the attached worksheet. I had to delete some of the rows to keep the size of the attachment down....

    You should be able to make this work for a larger sample as well. Post back if your confused.

    Ger
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Ger Plante; February 18th, 2007 at 05:40.
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  10. #10
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Formula: Change Fill Color If Row Contains Data Common With Another Sheet

    I'll find out how to do this tomorrow - it's been posted here in the last week, but I can't do it tonight.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Conditional Formatting Across Worksheets
    By bleeg10 in forum EXCEL HELP
    Replies: 11
    Last Post: December 20th, 2012, 05:08
  2. Conditional Formatting Across 2 Worksheets
    By anewhope in forum EXCEL HELP
    Replies: 3
    Last Post: April 5th, 2008, 00:59
  3. Replies: 4
    Last Post: April 7th, 2005, 00:54

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
  •  
porno