Cell format from value in different sheet

Urlord

Board Regular
Joined
Aug 5, 2010
Messages
130
I have a workbook where I would like to highlight cell A1 in sheet one if any cell in column d on sheet 2 is not blank.

Thanks
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
You can do this using conditional formatting.

How to set it up depends on what version of Excel you're using?
 
Upvote 0
You can do this using conditional formatting.

How to set it up depends on what version of Excel you're using?

I am using 2007 at work. I figured it could be done using CF but can not seem to figure it out
 
Upvote 0
Ok, Excel 2007 doesn't allow you to directly reference another sheet when applying cf so you have to trick it by creating a defined named range that refers to the range on the other sheet.

So, navigate to Sheet2 and select the entire column D. In the name box, that little "box" immediately above the column A column header where it shows the cell address, type in a name for the range. Maybe something like Sh2ColD. Type in the name then hit Enter.

Navigate to Sheet1 and select cell A1 to set up the cf.

Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format

Enter this formula in the box below:

=COUNTA(Sh2ColD)<>0

Click the Format button

Select the desired style(s)

OK out
 
Upvote 0
Ok, Excel 2007 doesn't allow you to directly reference another sheet when applying cf so you have to trick it by creating a defined named range that refers to the range on the other sheet.

So, navigate to Sheet2 and select the entire column D. In the name box, that little "box" immediately above the column A column header where it shows the cell address, type in a name for the range. Maybe something like Sh2ColD. Type in the name then hit Enter.

Navigate to Sheet1 and select cell A1 to set up the cf.

Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format

Enter this formula in the box below:

=COUNTA(Sh2ColD)<>0

Click the Format button

Select the desired style(s)

OK out
This worked great.
Now is is possible to CF a cell in Sheet1 if a specific cell in sheet 2 contains a specific value.

Thanks
 
Upvote 0
I found a workaround for this.

Thanks
The procedure would be pretty much the same...
  • On Sheet2 create a defined name that refers to cell in question
  • On Sheet1 select the cell you want formatted
  • Conditional Formatting>Formula: =named_cell=this_value
  • Set the desired style(s)
 
Upvote 0
The procedure would be pretty much the same...
  • On Sheet2 create a defined name that refers to cell in question
  • On Sheet1 select the cell you want formatted
  • Conditional Formatting>Formula: =named_cell=this_value
  • Set the desired style(s)

Since I know the values that the cell could possibly be I used
=CONCATENATE(Sheet2!E2," ",Sheet2!F3)

I the applied the following CF
=countif($b2,"*out of service*")

This produced the desired result

Thanks
 
Upvote 0

Forum statistics

Threads
1,224,514
Messages
6,179,225
Members
452,896
Latest member
IGT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top