Conditional Formatting using 2 worksheets

martina77 · Apr 9th, 2002, 01:38 AM

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 ]

Derek · Apr 9th, 2002, 02:00 AM

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 ]

sen_edp · Apr 9th, 2002, 02:10 AM

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

martina77 · Apr 9th, 2002, 02:10 AM

Thank you very much Derek. I'll give that a go. Martina

)

Tommy Bak · Apr 9th, 2002, 02:18 AM

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

Dave Hawley · Apr 9th, 2002, 03:28 AM

Hello Martina

If you follow this link to my site: http://www.ozgrid.com/Excel/Formulas.htm you will see another link to a MS page that should assist you greatly.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 01:38 AM	#1
martina77 New Member Join Date: Apr 2002 Location: Northern Ireland Posts: 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 ]

Apr 9th, 2002, 02:00 AM	#2
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	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 ]

Apr 9th, 2002, 02:10 AM	#3
sen_edp Board Regular Join Date: Mar 2002 Location: Hellas Posts: 553	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

Apr 9th, 2002, 02:18 AM	#5
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	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

Apr 9th, 2002, 03:28 AM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hello Martina If you follow this link to my site: http://www.ozgrid.com/Excel/Formulas.htm you will see another link to a MS page that should assist you greatly.