Formatting main sheet to reflect data in another cell in a hidden sheet

[irishx]

I know that subject line could be very confusing, so I will try to explain the best I can.

I have a main sheet that displays all the programs within my work center. I have hyperlinks that once you click on them, they route you to the sheet with that programs information. What I'm trying to accomplish is to have the cell on the main page that is hyperlinked to change colors based on the data in a cell on the hidden sheet. Any and all help is appreciated. Thank you for your time!

[#2]

@irishx 

That can be accomplished using a conditional formatting rule of type 'Use a formula to determine which cells to format'.

The formula can refer to the cell on the hidden sheet.

If you would like more help, please provide detailed information about what you want to do.

[#3]

Right, so I've attempted that by selecting the cell on the main sheet that has the program title hyperlinked to the sub sheet. Then I Conditional Format as such: =if(and('subsheet'!$a$1=today()-30,'subsheet'!$a$1=today()+30)

The formatting on the subsheet is set to show when the date inputted turns the cell red if it's within 30 days or past due. It also has the cell turn yellow when it's 30-90 days out. Basically I am just trying to get the main sheet with the hyperlinked cell to display the color of the cell in the subsheet should the data change.

[#4]

@irishx 

Remove the current rules.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

='subsheet'!$A$1<=TODAY()+90

Click Format...
Activate the Fill tab.
Select yellow as highlight color.
Click OK, then click OK again.

Repeat these steps, but with the formula

='subsheet'!$A$1<=TODAY()+30

and red as highlight color.