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, 8 views)

Hi and welcome
please post a sample sheet with some data and expected results ( click Go advanced - Manage attachments)
Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I have added the file as an attachment to the original post. I want the boxes populated on the front sheet to automatically populate on the following sheets when filled in. There is some conditional formatting on each sheet which I would like to keep on this sheets. The colours mean something. The problem is, when I use a formula on the other sheets to try and pull the data from the front sheet the box shows a colour. I don't want to see a colour until there is data from the front sheet to see.

I think you just need to adjust your conditional formats to use a formula instead...


e.g.


For the red colour, instead of selecting "Format only Cell that Contains", select "use a formula to determine....."


Then apply formula:



=AND(D4>0,TRIM(D4)<>"")

Thank you for this formula. It has worked on the first page perfectly, but the other two seem to be colouring in the boxes 4 down. Do I need to modify the formula? I have attached the sheet so you can see.

Attached Files

test two.xlsx (38.6 KB, 8 views)

You need to adjust the formula to the top left most cell of the range that you are applying the condition to... so probably:


=AND(D8>0,TRIM(D8)<>"")

This works great if I fill the cells in on that page but when the formula I have put into the each cell brings the data across from the front tab the cell does not colour but the text shows

Attached Files

test two.xlsx (38.6 KB, 7 views)

You had a mix of conditional formats in the first sheet.


You need to clear all the formats and try again.


See attached. I did it for all sheets in the attached.

Attached Files

Copy of test two.xlsx (39.7 KB, 7 views)

amazing!!!!!!!!!! THANK YOU SO MUCH!