HI Experts,
I have 2 tabs in 1 excel file, both tab have same column, I need in 2nd tab which data is not entered in 1st tab that will highlight in 2nd tab,
Thanks in advance.
Far
HI Experts,
I have 2 tabs in 1 excel file, both tab have same column, I need in 2nd tab which data is not entered in 1st tab that will highlight in 2nd tab,
Thanks in advance.
Far
This can be done with conditional formatting and the MATCH command. =ISNA(MATCH(A1,'1st'!$A$1:$A$18,0)) Match looks for the value in A1 in the range A1:B18 on the first sheet. If it finds it, it returns the row number on which it was found. If it does not find it, it returns #N/A.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
In your 2nd tab, A1, go to Conditional Formatting. New Rule. Use a formula.
Enter:Format as desired. Copy your format down with the format painter.Please Login or Register to view this content.
Pete
Last edited by PeteABC123; 01-02-2019 at 05:20 PM.
Hi pete, I have put formula it is showing error "you cannot use reference to other worksheets or workbooks for conditional formatting criteria"
dfla, I have tried but it is not working
You must use named ranges:
FirstSheet ='1st'!$A$1:$A$18
SecondSheet ='2nd '!$A$1:$A$21
then conditional format formula (on the first sheet) looks like:
=ISNA(MATCH(A1,SecondSheet,0))
Ben Van Johnson
Minor correction. Try this in CF:
PetePlease Login or Register to view this content.
Last edited by PeteABC123; 01-02-2019 at 06:09 PM.
Must be a 2007 thing. It works in 2013 and 2016. Named ranges are the way to go.
tried but not working, can you please send me in Excel file?
I'm using 2007 version
File attached.
Please Login or Register to view this content.
Thank You protonLeah
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks