# Thread: Help with auto-highlight box based on criteria from another worksheet

1. ## Help with auto-highlight box based on criteria from another worksheet

Hello everyone,

I am hoping I can get some help with a problem I have, as my Excel skills are rusty and I can't seem to get my head around the logic required for this formula.

I have a workbook with numerous sheets. One main sheet lists details of people, and one column in this sheet has a hyperlink which links to each person's individual sheets.

Like so:

Main Sheet
A B C D
Julie 10 Go Street Lots JulieK`!A1

Referenced Sheet

A B C D E
Date Type Detail 31/3 N

So for the referenced sheet, I record say a convo by date, whether it was email or Skype, the detail. Column D is whether it requires follow up, and a date by if it does. Then column E is either Y or N, for complete or not complete.

What I want to figure out how to do is, if there is an N entries in any of the cells of column E on the JulieK sheet (or any other person's individual sheet), that the Notes cell for that person (column D) on the main sheet will turn red so I can just scan down the main page and see if there are any outstanding things on each person's individual page.

I hope that makes sense, and I would be very grateful if someone could help me figure this out

Julie

2. Setup conditional formatting in D2:Dn with a formula of

=COUNTIF(INDIRECT(LEFT(D2,FIND("!",D2))&"E:E"),"N")

and a format to suit.

3. Thanks so much for the reply Bob, however I'm not sure how that references the second sheet? If the formula is put in D2 on worksheet 1, yet it's referencing column E on sheet 2, I can't see how it's referencing? Thanks again for your help.

Julie

Originally Posted by Bob Phillips
Setup conditional formatting in D2n with a formula of

=COUNTIF(INDIRECT(LEFT(D2,FIND("!",D2))&"E:E"),"N")

and a format to suit.

4. Julie, I am assuming that your hyperlink text is as per your example, so I get the sheet name from that.

Did you try it?

I did try it - I'm sure it's my fault not yours, just trying to understand it properly so I can extrapolate for other usage. Thanks again Bob.

Originally Posted by Bob Phillips
Julie, I am assuming that your hyperlink text is as per your example, so I get the sheet name from that.

Did you try it?

6. Post the workbook, and let's see what's up.

7. Hi Bob,

I've attached a dummy workbook. You can see on the Contractor Details worksheet in column K, there is a notes hyperlink, which links to an individual worksheet. The first two contractors have current tasks listed on their sheets. So Contractor 1 has an item with a due date of 10/4 for completion, and an N in column F.

What I want to be able to do is, when I look down column K on the Contractor Details worksheet, if there is an outstanding item on an individual worksheet (denoted by the "N" for not complete, in column F), the cell with the hyperlink on the Contractor Details worksheet becomes highlighted.

I hope this makes sense. What I'm trying to achieve is a flag so that when I open the workbook, I can easily scan column K to see whose worksheets I need to look at to look for follow up items, rather than having to access each individual sheet.

Julie

test.xlsx

8. Julie, the CF formula that I gave you works perfectly, although it does need a bit of tweaking to match your actual data

=COUNTIF(INDIRECT(LEFT(K4,FIND("!",K4))&"F:F"),"N")

If you change the cells on Contractor Details to a table, any new items will automatically inherit that CF.

#### Posting Permissions

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