Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Conditional Formatting using 2 worksheets

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Northern Ireland
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not technical please. No VBA code please. I am trying to use conditional formating using info. from 2 different worksheets.It did not allow me.I then tried naming the cell in 1 but the condition that I entered, although acknowledged, worked the wrong way around.

    _________________
    Martina Welch

    [ This Message was edited by: martina77 on 2002-04-09 00:56 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Martina

    Perhaps you can link the data together on one worksheet and then apply the conditional formatting. Try copying the data in one book and pasting it as a link into the sheet of the other book. Then apply your conditional formatting to both sets of data on the one sheet.
    Hope this helps
    regards
    Derek
    PS Sorry, I thought you were talking about different workbooks but the answer is the same, conditional formatting only works on one sheet, so you have to bring the other data to that sheet first. You could put it in hidden columns.

    [ This Message was edited by: Derek on 2002-04-09 01:08 ]

  3. #3
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hello there

    Custom functions in Conditional Formatting cannot reference cells in other worksheets in the same workbook, and cannot reference cells in other workbooks. However, you can get around this limitation by using defined names. Create a defined name which refers to the list in the other workbook or worksheet, and then use that name in your custom function.
    For example, suppose you want to make cell A1 on Sheet1 red if that cell's entry is not found on a list on Sheet2, cells B1:B10. If you tried to use the formula =COUNTIF(Sheet2!$B$1:$B$10,A1)=0 as your formula, you would receive an error message from Conditional Formatting. To get around this error, create a defined name called MyList which refers to the range =Sheet2!$B$1:$B$10 and use the name in your custom formula:
    =COUNTIF(MyList,A1)=0

    Andreas



  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    Northern Ireland
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you very much Derek. I'll give that a go. Martina )

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I don't know exactly what you are trying to do, but this example checks if the value added in cell A1 is in sheet1!b2:b5 or in sheet2!b2:b5.
    First give both ranges names ex. cond1 and cond2
    then use conditional formats:
    Formula is
    =countif(cond1, a1) + countif(cond2, a1) > 0

    and choose you format and OK.

    Now A1 is formatted as you like if the value is in cond1 or cond2
    The trick here is the use of named ranges.

    regards Tommy

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello Martina

    If you follow this link to my site: http://www.ozgrid.com/Excel/Formulas.htm you will see another link to a MS page that should assist you greatly.



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •