Macro that links values for conditional formatting from many worksheets to one main sheet

Hello,

I have a Main sheet that collects the information that i put in dedicated sheets for different projects. These Project sheets have a conditional formatting in form of a red/yellow/green icon that is controlled when to turn the different colors individually with the help of a box (that i call "indicator control") on every project's sheet. I would like the conditional formatting that is on the project sheets to also be on the main page for each of the projects. As i understand the only way of doing this would be with a macro that automatically creates the correct conditional formatting icon and links every project row to the right project sheets "indicator control" - the problem is that i don't know how to write this macro. If any of you have any idea or some other way of dealing with this problem that produces the a similar outcome i would be eternally grateful!

I included an example with two projects (in reality it's several hundred) of how i want it to look and behave.

Thanks in advance!

/Adam

Hi MM,

Thanks for answering so fast! The code works fine except it overwrites the formula that i had in each of the cells on the Main sheet. I updated the document with the formulas I’m using to get the numbers from each of the Project sheets to the Main sheet. It uses the Name row as a way of referencing to the right Project sheet, as the numbers in the projects is changing from time to time the formula is more handy than "hard"copying the numbers with help of the push of a macro button, but if this is the only way of doing this perhaps i need to replan this project.

There is no way to tell a macro to use the Name row as a reference to a worksheet and then link the conditional formatting Value to a specific cell on this Project sheet and then go down another row on the Main sheet and to the same thing again with help of the Name row?

The formula I’m using on the Main sheet looks something like this: =IF(INDIRECT("'"&A2&"'!b2")="";"";INCIDIRECT("'"&A2&"'!B2"))