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!

 

[sudheesh]

You can use a formula to determine the formatting for the cells.

 

[Mixer2]

Any idea how to go about writing the formula for this then?

 

[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!

I am still working on this, any ideas?

 

[shyy]

Before anything lets start from the basic use of conditional formatting

=IF(Sheet2!A1>4500,TRUE,FALSE)

Try this and see if it is highlighting cells that greater than 4500. Remeber the cells referenced has to be in the exact same cells but in a different sheet. Standardization is important for you what you want to accomplish.

 

[Mixer2]

OK I tried that using a similar formula:

=IF('[5-1-13 A4 Brown Janesville Exposed G.xls]BATCH LOG REPORT - TEMPLATE'!$M$60>4500,TRUE,FALSE)

The number was 6000 something so the cell read TRUE.

Now, I need it to look at three different cells in the other spreadsheet, m55, m56, and m60. If any of them are over 4500 then I want them to highlight the cell back on the original spreadsheet green. If none of them are higher than 4500 then highlight red. With all this, I still need the date typed in to be visible if possible.

Thanks for the help so far.

 

[Mixer2]

Any other suggestions Shyy? Or anyone else?

 

[shyy]

My logic might not make sense..

If the cell entered is higher than 4500, you would color blue, but doesn't the other two criteria always override regardless if the number is greater than 4500?

When my cells O60 - Q60 are less than 4500, it colors red and any cell higher than 4500 is colored green. Can't seem to get the blue color at all, my logic must be off.

Firs the first rule I created =IF(N59>4500,TRUE,FALSE) fill in blue

Second rule =IF(OR(O60>4500,P60>4500,Q60>4500),1,0) fill in green

Third rule =IF(AND(O60<4500,P60<4500,Q60<4500),1,0) fill in red

 

[Mixer2]

Not sure where you got blue from but I only need cells to highlight in green or red. Green if >4500 and red of <4500.

The rules arent that hard to figure out for me, its getting the conditional formatting to do its work dependant on info in cells from another spreadsheet.

 

[shyy]

Change the range to match your sheet, this should work. If all the cells are below 4500 it will color red and if anyone of them is higher than 4500 will color green


first rule =IF(OR(Sheet2!A1>4500,Sheet2!B1>4500,Sheet2!C1>4500),1,0 fill in green

second rule =IF(AND(Sheet2!A1<4500,Sheet2!B1<4500,Sheet2!C1<4500),1,0) fill in red