Is there a way to link Data and Formatting between sheets in a workbook?

Question

I have a workbook with 3 sheets.  The first sheet has 300 names in it.  The second sheet has a subset of 125 of the names.  The 3 sheet has a subset of 250 of the names.  I'd like to be able to highlight (fill) the individual names in the various cells on sheet 1 with red or yellow and have that fill color update on the corresponding names on sheet 2 or 3.  (Note: If there is a name on sheet 1 that gets highlighted and there is not a corresponding name on sheet 2 or 3, that's ok.)  Any ideas?

Answer 1

Hi PeterFri,

Try this

1.Select cell range on sheet1 and go to Home > Conditional formatting > Use a formula to determine which cells to format

2.Enter this formula there:

=COUNTIF(Sheet2!A$1:A$5,A1)>0 and for sheet3 =COUNTIF(Sheet3!A$1:A$5,A1)>0

3.Click on Format > Fill > Red

4.Click on OK > Apply

Let us know if you need further assistance.

Best Regards,

Waqas Muhammad

Answer 2

Thanks Muhammad! I tried your solution, but was unable to achieve the results I was looking for.  I played around with it for quite some time.

I am looking to be able to highlight the names on sheet 1 and have the corresponding highlight color show up on the names on sheet 2 and 3.  It seems like it should be something that isn't too difficult to accomplish, am beginning to think that what I am attempting to do can't be done under the current capabilities of MS Excel.

I am open to other ideas that anyone has.

Answer 3

Hi PeterFri,

Regarding to your scenario, you mention "highlight the names on sheet 1 and have the corresponding highlight color show up on the names on sheet 2 and 3" then you can apply conditional formation in sheet2 and sheet3, with respect to sheet1 e.g. enter this formula the in sheet2 is =COUNTIF(Sheet1!A$1:A$5,A1)>0 and enter this formula in sheet3 =COUNTIF(Sheet1!A$1:A$5,A1)>0, it will highlight color those name having in sheet1.

Let us know if you need further assistance.

Best Regards,

Waqas Muhammad

Answer 4

Hi PeterFri,

Any update?

Best Regards,

Waqas Muhammad

Answer 5

Thanks for your follow-up!  No, I was not able to get it to work.  I tried on a simple workbook with only five names on each sheet.  The formula did not appear to work and when I set the "Format" to Red it turned the range on sheet 1 to Red but wouldn't allow any changes to color from that point forward, and certainly not on any subsequent sheets.  I don't know if the problem is with me (which I highly suspect) or with a limitation on Office 365.  I've had to move on and find a manual workaround since my deadline is on Sunday.  Thank you again for your efforts.