Conditional Formatting using 2 worksheets

martina77

New Member
Joined
Apr 8, 2002
Messages
2
Not technical please. No VBA code please. I am trying to use conditional formating using info. from 2 different worksheets.It did not allow me.I then tried naming the cell in 1 but the condition that I entered, although acknowledged, worked the wrong way around.

_________________
Martina Welch
This message was edited by martina77 on 2002-04-09 00:56
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi Martina

Perhaps you can link the data together on one worksheet and then apply the conditional formatting. Try copying the data in one book and pasting it as a link into the sheet of the other book. Then apply your conditional formatting to both sets of data on the one sheet.
Hope this helps
regards
Derek
PS Sorry, I thought you were talking about different workbooks but the answer is the same, conditional formatting only works on one sheet, so you have to bring the other data to that sheet first. You could put it in hidden columns.
This message was edited by Derek on 2002-04-09 01:08
 
Upvote 0
hello there

Custom functions in Conditional Formatting cannot reference cells in other worksheets in the same workbook, and cannot reference cells in other workbooks. However, you can get around this limitation by using defined names. Create a defined name which refers to the list in the other workbook or worksheet, and then use that name in your custom function.
For example, suppose you want to make cell A1 on Sheet1 red if that cell's entry is not found on a list on Sheet2, cells B1:B10. If you tried to use the formula =COUNTIF(Sheet2!$B$1:$B$10,A1)=0 as your formula, you would receive an error message from Conditional Formatting. To get around this error, create a defined name called MyList which refers to the range =Sheet2!$B$1:$B$10 and use the name in your custom formula:
=COUNTIF(MyList,A1)=0

Andreas
 
Upvote 0
Hi
I don't know exactly what you are trying to do, but this example checks if the value added in cell A1 is in sheet1!b2:b5 or in sheet2!b2:b5.
First give both ranges names ex. cond1 and cond2
then use conditional formats:
Formula is
=countif(cond1, a1) + countif(cond2, a1) > 0

and choose you format and OK.

Now A1 is formatted as you like if the value is in cond1 or cond2
The trick here is the use of named ranges.

regards Tommy
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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