Hello,

I have a report that has various tabs. On each tab we have cells we populate to show an audit is due. We have some conditional formatting in these cells so they change color when something is entered into them.

Instead or going through all the sheets each time one of these cells need populated i decided to set up a front sheet.






On this sheet i was hoping to be able to populate the cells with he relevant letter and the conditional formatting and put a formula on the others sheets so it auto populates them. Because some of the cells have more than one report attached to them like this:



i tried to use the concatenate formula. I used this:
=CONCATENATE(Overview!D9," ",Overview!D10)


it worked great for the data and did what i hoped but it also brought the conditional formatting across, which meant even thought the cell was empty on the other tabs it was showing a colour because of the formula like this:



this takes away the effect of the cells being coloured in because they require an audit. How do i get round this? I would only want the cell to colour when the formula pulls through the information from the front tab.

Attached Files

Testing sheet.xlsx (61.1 KB, 10 views)