Conditional Formatting using info from cells in another spreadsheet

[Mixer2]

I am trying to figure out how to highlight a cell from one spreadsheet based on if the number in certain cells of another spreadsheet is greater or less than 4500. In spreadsheet 1 I have dates listed in columns, with those dates hyperlinked to the spreadsheet I want to base my highlight numbers on.

I have tried going into Conditional Formatting, then Highlight Cells Rules, then Greater Than...I then would type in 4500 and OK.

Then I go back into Conditional Formatting with the cursor still in that cell and go down to manage rules. Then under the rule I just put in, I click the box under applies to. I then go into the other spreadsheet and click the cell I want to use for my value of 4500. But when I hit OK to finish, it always goes back to the cell that the cursor is on in the original spreadsheet.

My ultimate goal is to have the cells in the original spreadsheet 1 be either highlighted green if over 4499 or red if under 4500, based on up to 3 cells in other spreadsheets that will be hyperlinked to the date entered in spreadsheet 1. I need the date to be visible where I enter it in spreadsheet 1 as well.

Any answers or help for me? Thanks!

 

[uk747]

=IF('[5-1-13 A4 Brown Janesville Exposed G.xls]BATCH LOG REPORT - TEMPLATE'!$M$60>4500,TRUE,FALSE)
=IF('[5-1-13 A4 Brown Janesville Exposed G.xls]BATCH LOG REPORT - TEMPLATE'!$M$60<4500,TRUE,FALSE)
then enter your range where you want in the applies to field

 

[Mixer2]

Where would you enter these then? The rules for conditional formatting?

 

[Mixer2]

I think this needs to be entered in the formula bar, but how do you enter it so that it would keep the text you want showing? For instance I want a certain date to be what is showing in the cell, but still do the conditional format from your functions?

ie: 5-1-13

 

[shyy]

The conditional formatting doesn't matter where the cell with the date is in. It uses the other cells to base its conditional formatting from. Remember to make the cells relative/absolute depending on your needs.

Can you elaborate again or post a sample of what you mean regarding the date?

 

[Mixer2]

Basically what I have is a list of dates when each job is done on one spreadsheet. I have that date hyperlinked to that jobs batch report on other spreadsheets. The batch report is where the numbers are that I am basing my conditional formatting on. So when viewing the original sheet, I need the dates to be visible in the cells, not the numbers that the conditional formatting is based on or the formula. I hope that explains it some.