+ Reply to Thread
Results 1 to 9 of 9

Matching cells across sheets and returning color value based on the sheet

  1. #1
    Registered User
    Join Date
    12-24-2013
    Location
    NYC
    MS-Off Ver
    Excel 2010
    Posts
    4

    Matching cells across sheets and returning color value based on the sheet

    I have a set of sheets on a workbook that contain addresses, which are all combined on the first sheet (as you can see in the attached file). I would like to use a formula to compare the addresses on the Main sheet with the other sheets, then return in the D column of Main a colored cell indicating on which sheet the address was found on; different colors for different sheets. I tried to figure out some conditional formatting, but to no avail.
    Also, on occasion I have spreadsheets with 5 or 6 sheets containing these mixed addresses. Is there a solution that won't be limited to just a few sheets for comparison?

    Excel Help.xlsx


    Thanks
    Last edited by stefanapoli; 12-24-2013 at 03:14 PM.

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Matching cells across sheets and returning color value based on the sheet

    Instead of returning a colored cell why not just return the sheet name?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    12-24-2013
    Location
    NYC
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Matching cells across sheets and returning color value based on the sheet

    Thanks Tony,

    The spreadsheet is going straight to print. Cell D needs to be a color-code cell, only 12 pixels or so wide, so no room for a sheet name in the cell.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Matching cells across sheets and returning color value based on the sheet

    How about returning the sheet name to cells that are out of sight in an unused area of the sheet? Say column AA? Then, you can use conditional formatting in column D based on the sheet name in column AA.

  5. #5
    Registered User
    Join Date
    12-24-2013
    Location
    NYC
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Matching cells across sheets and returning color value based on the sheet

    Yeah, I think that would do. What formula could I use to do this?

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Matching cells across sheets and returning color value based on the sheet

    Try this...

    Create this defined named formula...

    Goto the Formulas tab>Define name
    Name: SheetNames
    Refers to: =GET.WORKBOOK(1)&T(NOW())
    OK out

    Then, with your addresses on the Main sheet in the range A2:A9...

    Enter this formula in say AA2 and copy down to AA9:

    =LOOKUP(2,1/COUNTIF(INDIRECT("'"&SheetNames&"'!A2:A10"),A2),INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),0))

    Then, you can use conditional formatting in column D based on the sheet name in column AA.

    I applied this in your sample file as:

    Sheet1 = a shade of green
    Sheet2 = a shade of red (maroon?)
    Sheet3 = a shade of blue

    You'll have to save the file as a macro enabled file in the *.xlsm file format.

    Here's your file with this implemented:

    Excel Help (1).xlsm

  7. #7
    Registered User
    Join Date
    12-24-2013
    Location
    NYC
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Matching cells across sheets and returning color value based on the sheet

    Thank you very much! It works wonderfully!

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Matching cells across sheets and returning color value based on the sheet

    Improvement!

    We can shorten the sheet name formula a few keystrokes:

    =LOOKUP(2,1/COUNTIF(INDIRECT("'"&SheetNames&"'!A2:A10"),A2),MID(SheetNames,FIND("]",SheetNames)+1,31))

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Matching cells across sheets and returning color value based on the sheet

    You're welcome. Thanks for the feedback!

    If your question has been answered please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools>Mark this thread as solved.

+ 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] Matching Cells in multiple sheets then returning the value of a cell in the same row..
    By AdamCassar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-08-2013, 09:31 PM
  2. Replies: 0
    Last Post: 01-04-2013, 12:25 PM
  3. Replies: 4
    Last Post: 05-24-2012, 06:44 AM
  4. Replies: 1
    Last Post: 07-14-2010, 07:08 PM
  5. Replies: 5
    Last Post: 04-13-2007, 06:58 PM

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