Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How can I use conditional formating between two sheets in one work
If employee number 5263 found in sheet 2, show the raw in main data on sheet
1 in red, for instance. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How can I use conditional formating between two sheets in one work
ok this is how i did it
use a helper column and a vlookup formula back at sheet 2 =IF(VLOOKUP($G$11,Sheet2!$A$2:$A$8,1,FALSE)=A8,"fo und","") so if employees name is found on sheet 2 this formula returns found on the same row as the emplyees name conditional formatting for this row is =c8="found" i had to change the formatting formula for each row,hopefully there is an easy way! -- paul remove nospam for email addy! "Samad" wrote: If employee number 5263 found in sheet 2, show the raw in main data on sheet 1 in red, for instance. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How can I use conditional formating between two sheets in one work
"Samad" wrote:
If employee number 5263 found in sheet 2, show the raw in main data on sheet 1 in red, for instance. In Sheet2, assume the employee numbers would be listed within B1:B200 (say) In Sheet1, assume the employee numbers are listed in A1 down Select col A (A1 active) Click Format Conditional Formatting, then apply in Condition 1: Formula is: =SUMPRODUCT((ISNUMBER(SEARCH(A1,INDIRECT("Sheet2!$ B$1:$B$200")))*(A1<""))) Format it as, eg: Red fill & white font, bolded The above will achieve what you're after, ie highlight all employee numbers in col A which are found within Sheet2's B1:B200. Adapt the range to suit .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How can I use conditional formating between two sheets in one work
Conditional formatting wont allow you to reference another worksheet, however, as a work around you could add a column and paste in the formula =NOT(ISERROR(VLOOKUP(A1,Sheet2!A1:A7,1,FALSE))) Where cell A1 contains the employee number and Sheet2!A1:A7 contains the employee numbers on the other sheet. This formula will return TRUE if the employee is on the other sheet and FALSE if it is not. You can then hide this column if you want and use the result in your conditional formatting by highlighting the range you want to conditionally format and for the first record say you put the formula above in column D you would select Formula and type in =$D1 and select the red font format. -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=566255 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How can I use conditional formating between two sheets in one work
You can reference another worksheet if you use a named range. For example, if your employee numbers are in sheet 2 column A then select that column and use Insert Name Define to name that range, something like "employees" (without the quotes) then in sheet 1 if your employee numbers are in column C, select that column and use CF with "formula is" option =MATCH(C1,employees,0) choose red format that's it -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=566255 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How can I use conditional formating between two sheets in one
"Excelenator" wrote:
Conditional formatting won't allow you to reference another worksheet, .. True, but using INDIRECT is a way to do so "directly" which is accepted in CF, as per earlier response to OP. Another route is to use defined ranges. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How can I use conditional formating between two sheets in one work
David McRitchie posted this message June 11,2006
You may not use references to other worksheets or Workbooks for Conditional Formatting criteria. To get around this obstacle you might use a named range to refer to a range on another worksheet. The restriction also means that you cannot use a formula referencing your personal.xls in a user defined function. You can get around that by creating a reference in your VBE from your workbook to your personal.xls For more information on Conditional Formatting: http://www.mvps.org/dmcritchie/excel/condfmt.htm For more information on named range http://www.mvps.org/dmcritchie/excel...htm#namedrange Gord Dibben MS Excel MVP On Sat, 29 Jul 2006 18:34:29 -0400, Excelenator wrote: Conditional formatting wont allow you to reference another worksheet, however, as a work around you could add a column and paste in the formula =NOT(ISERROR(VLOOKUP(A1,Sheet2!A1:A7,1,FALSE))) Where cell A1 contains the employee number and Sheet2!A1:A7 contains the employee numbers on the other sheet. This formula will return TRUE if the employee is on the other sheet and FALSE if it is not. You can then hide this column if you want and use the result in your conditional formatting by highlighting the range you want to conditionally format and for the first record say you put the formula above in column D you would select Formula and type in =$D1 and select the red font format. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
validate work sheets | Excel Worksheet Functions | |||
Conditional Formating | Excel Worksheet Functions | |||
Conditional Formating | Excel Discussion (Misc queries) | |||
Conditional Formating Extreme Question | Excel Worksheet Functions | |||
How to change an entire row using conditional formating | Excel Discussion (Misc queries) |