SOLVED

Conditional Formatting over different sheets in a workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-1516931%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20over%20different%20sheets%20in%20a%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516931%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%20a%20workbook%20with%209%20Different%20sheets%2C%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20sheet%20is%20for%20a%20different%20case.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EFor%20Example%3A%3C%2FSTRONG%3E%20The%20workbook%20is%20for%20our%20products%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet1%20%3D%20Orders%20which%20are%20completed%3C%2FP%3E%3CP%3ESheet2%20%3D%20Orders%20ready%20for%20billing%3C%2FP%3E%3CP%3ESheet3%20%3D%20Orders%20with%20technical%20issues%3C%2FP%3E%3CP%3Esheet4%20%3D%20Orders%20with%20mechanical%2Freplacement%20Issues%3C%2FP%3E%3CP%3Eand%20so%20on..%2C%3C%2FP%3E%3CP%3EEvery%20sheet%20has%20few%20columns%20but%20in%20all%20the%20sheets%2C%20there%20are%203%20columns%20which%20are%20common%2C%3C%2FP%3E%3CP%3E%3CSTRONG%3EA%3A%3C%2FSTRONG%3E%20Order%20Number%26nbsp%3B%20%3CSTRONG%3EB%3A%3C%2FSTRONG%3E%26nbsp%3BLocation%26nbsp%3B%20%3CSTRONG%3EC%3A%3C%2FSTRONG%3E%20Date%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20i%20want%20to%20establish%20is%2C%20If%20Order%205674%20is%20entered%20in%20%3CSTRONG%3Ecolumn%20A%3C%2FSTRONG%3E%20of%20%22Ready%20for%20Billing%20%22%20sheet%20and%20if%20i%20mistakenly%20entered%20the%20same%20order%20number%20in%20%3CSTRONG%3EColumn%20A%20%3C%2FSTRONG%3Eof%26nbsp%3B%22Technical%20Issues%22%26nbsp%3B%20it%20should%20color%20the%20cell%20in%20RED%20saying%20its%20a%20Duplicate.%3C%2FP%3E%3CP%3E(%20I%20cannot%20use%20any%20kind%20of%203rd%20party%20tools%20or%20software%20but%20i%20want%20to%20achieve%20this%20using%20conditional%20formatting%20or%20writing%20a%20module)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20Upfront%20for%20your%20time.%20Your%20Ideas%20are%20greatly%20appreciated.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1516931%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517078%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20over%20different%20sheets%20in%20a%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517078%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725726%22%20target%3D%22_blank%22%3E%40FlexYourDAta%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThankyou%20so%20much%20for%20your%20efforts.%20Really%20appreciate%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abhishek_gliot_0-1594566897155.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204817i2AB067A598D9AB54%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abhishek_gliot_0-1594566897155.png%22%20alt%3D%22Abhishek_gliot_0-1594566897155.png%22%20%2F%3E%3C%2FSPAN%3EPicture%201%3A%20Technical%20Issues%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abhishek_gliot_1-1594566920128.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204818i11D677B94C9B9D0C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abhishek_gliot_1-1594566920128.png%22%20alt%3D%22Abhishek_gliot_1-1594566920128.png%22%20%2F%3E%3C%2FSPAN%3EPicture%202%3A%20Ready%20for%20billing%3C%2FP%3E%3CP%3EI%20exactly%20did%20what%20you%20have%20done%20(%20Applied%20formula%20in%20order%20column%2C%20for%20Tech%20Issues%20sheet)%20and%20still%2C%20this%20is%20what%20i%20am%20seeing.%20Am%20i%20doing%20some%20thing%20wrong%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517134%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20over%20different%20sheets%20in%20a%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517134%22%20slang%3D%22en-US%22%3EHi%2C%20please%20add%20a%20screenshot%20of%20your%20conditional%20formatting%20rule%20and%20paste%20the%20exact%20formula%20in%20the%20rule%20into%20a%20code%20snippet.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517158%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20over%20different%20sheets%20in%20a%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517158%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abhishek_gliot_0-1594571556745.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204822i8FE2506B2305B224%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abhishek_gliot_0-1594571556745.png%22%20alt%3D%22Abhishek_gliot_0-1594571556745.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3DNOT(ISERROR(VLOOKUP(%24A3%2C'Ready%20for%20Billing'!%24A%243%3A%24A%245%2C1%2CFALSE)))%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725726%22%20target%3D%22_blank%22%3E%40FlexYourDAta%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517231%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20over%20different%20sheets%20in%20a%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517231%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F726130%22%20target%3D%22_blank%22%3E%40Abhishek_gliot%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOk%2C%20since%20you%20are%20starting%20with%20data%20in%20the%20table%2C%20please%20go%20to%20the%20Conditional%20Formatting%20Rules%20Manager%20using%20Home%26gt%3BConditional%20Formatting%26gt%3BManage%20Rules.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20click%20the%20arrow%20to%20edit%20the%20Applies%20To%20range%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22edit%20Applies%20To.png%22%20style%3D%22width%3A%20623px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204823i3E3B6F24FF889D4B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22edit%20Applies%20To.png%22%20alt%3D%22edit%20Applies%20To.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20put%20your%20cursor%20above%20the%20%22order%22%20column%20until%20you%20see%20a%20black%20arrow.%20When%20you%20see%20the%20black%20arrow%2C%20left-click%20so%20that%20the%20whole%20column%20is%20selected%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FlexYourDAta_0-1594572143619.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204824i3B34AC3FF3E00D0E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22FlexYourDAta_0-1594572143619.png%22%20alt%3D%22FlexYourDAta_0-1594572143619.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20click%20the%20down-arrow%20on%20the%20Conditional%20Formatting%20Rules%20Manager%20shown%20above.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20select%20the%20rule%20and%20click%20Edit%20Rule.%3CBR%20%2F%3E%3CBR%20%2F%3ESelect%20the%20part%20of%20the%20formula%20that%20says%20%24A3%20and%20then%20click%20on%20the%20first%20row%20in%20the%20technicalissues%20table.%20For%20you%2C%20this%20will%20be%20changing%20it%20to%20%24A2%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FlexYourDAta_1-1594572260087.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204825iDD282B91C192E37E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22FlexYourDAta_1-1594572260087.png%22%20alt%3D%22FlexYourDAta_1-1594572260087.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWhen%20you%20first%20click%20the%20cell%2C%20it%20will%20show%20%24A%242.%20You%20need%20to%20remove%20the%20second%20%24.%20You%20can%20do%20this%20by%20deleting%20it%2C%20or%20by%20pressing%20F4%20twice%20to%20cycle%20through%20relative%20and%20absolute%20references.%3CBR%20%2F%3E%3CBR%20%2F%3ENext%2C%20highlight%20the%20part%20of%20the%20formula%20that%20gives%20the%20range%20to%20the%20Ready%20for%20Billing%20sheet%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FlexYourDAta_2-1594572355366.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204826iF7B45F0A1AB8ED9D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22FlexYourDAta_2-1594572355366.png%22%20alt%3D%22FlexYourDAta_2-1594572355366.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhile%20that%20is%20highlighted%2C%20go%20to%20the%20Ready%20for%20Billing%20sheet%20and%20again%2C%20select%20the%20entirety%20of%20column%20A%20by%20clicking%20on%20the%20A%20column%20header%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FlexYourDAta_5-1594572656019.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204829i53BB6E8F86D3CEE8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22FlexYourDAta_5-1594572656019.png%22%20alt%3D%22FlexYourDAta_5-1594572656019.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20end%2C%20your%20rule%20formula%20will%20look%20something%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DNOT(ISERROR(VLOOKUP(%24A2%2C'Ready%20for%20billing'!%24A%3A%24A%2C1%2CFALSE)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3CBR%20%2F%3ENow%20that%20you%20have%20deliberately%20selected%20the%20entire%20column%20for%20the%20lookup%20range%2C%20and%20repositioned%20your%20starting%20cell%2C%20you%20should%20have%20no%20problems%20with%20newly%20added%20rows.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517052%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20over%20different%20sheets%20in%20a%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517052%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F726130%22%20target%3D%22_blank%22%3E%40Abhishek_gliot%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20created%20two%20sheets%3A%3C%2FP%3E%3CP%3E1.%20Ready%20for%20billing%3C%2FP%3E%3CP%3E2.%20Technical%20issues%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20each%20sheet%2C%20I%20created%20a%20Named%20Table%20(use%20Ctrl%2BT%20or%20Home%26gt%3BFormat%20as%20Table)%20with%20three%20columns%3A%3C%2FP%3E%3CP%3E1.%20Order%20number%3C%2FP%3E%3CP%3E2.%20Location%3C%2FP%3E%3CP%3E3.%20Date%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20tables%20are%20named%20(respectively)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20readyforbilling%3C%2FP%3E%3CP%3E2.%20technicalissues%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FlexYourDAta_0-1594565037999.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204812iBB2C7C886E172F7F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22FlexYourDAta_0-1594565037999.png%22%20alt%3D%22FlexYourDAta_0-1594565037999.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20place%20my%20cell%20in%20the%20first%20row%20of%20the%20technicalissues%20table%20in%20the%20Order%20number%20column%2C%20then%20I%20create%20a%20rule%20using%20Home%26gt%3BConditional%20Formatting%26gt%3BNew%20Rule%2C%20where%20I%20select%20%22Use%20a%20formula%20to%20determine%20which%20cells%20to%20format%22.%3C%2FP%3E%3CP%3EThe%20formula%20used%20in%20the%20rule%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DNOT(ISERROR(VLOOKUP(%24A3%2C'Ready%20for%20billing'!%24A%3A%24A%2C1%2CFALSE)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20am%20editing%20the%20second%20parameter%20of%20the%20VLOOKUP%20formula%2C%20I%20click%20the%20column%20header%20of%20the%20first%20column%20(A)%20on%20the%20Ready%20for%20Billing%20sheet%2C%20such%20that%20it%20selects%20the%20entire%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20likely%20know%2C%20a%20conditional%20formatting%20formula%20must%20return%20either%20TRUE%20or%20FALSE.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20is%20looking%20for%20the%20order%20number%20from%20the%20current%20row%20in%20the%20technicalissues%20table%20in%20the%20order%20number%20column%20of%20the%20readyforbilling%20table.%20If%20it's%20found%2C%20VLOOKUP%20will%20return%20the%20order%20number%20from%20column%201%20of%20the%20readyforbilling%20table%2C%20and%20so%20will%20not%20be%20an%20error.%20As%20such%2C%20the%20entire%20formula%20will%20return%20TRUE%20(because%20NOT%20inverts%20the%20return%20value%20of%20ISERROR%2C%20which%20would%20be%20FALSE).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20order%20number%20is%20not%20found%2C%20VLOOKUP%20will%20return%20an%20error%2C%20ISERROR%20will%20return%20TRUE%20and%20NOT(ISERROR%20will%20return%20FALSE.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20net%20effect%20is%20that%20order%20numbers%20found%20in%20the%20readyforbilling%20table%20are%20formatted%20and%20those%20which%20are%20not%2C%20are%20not.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBecause%20we%20are%20using%20Tables%2C%20the%20Applies%20To%20range%20will%20expand%20as%20each%20new%20row%20is%20added.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22conditionalformatacrosssheets.gif%22%20style%3D%22width%3A%20667px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204815iF2E6A37DF9FAB2BB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22conditionalformatacrosssheets.gif%22%20alt%3D%22conditionalformatacrosssheets.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517397%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20over%20different%20sheets%20in%20a%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517397%22%20slang%3D%22en-US%22%3E%3CP%3EThankyou%20so%20much%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725726%22%20target%3D%22_blank%22%3E%40FlexYourDAta%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20worked%20perfect%20and%20i%20am%20able%20to%20apply%20the%20same%20formula%20vice%20versa%20on%20all%20sheets.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThankyou%20for%20your%20time%20and%20efforts.%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20great%20day.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello

 

I use a workbook with 9 Different sheets, 

Each sheet is for a different case. 

For Example: The workbook is for our products,

 

Sheet1 = Orders which are completed

Sheet2 = Orders ready for billing

Sheet3 = Orders with technical issues

sheet4 = Orders with mechanical/replacement Issues

and so on..,

Every sheet has few columns but in all the sheets, there are 3 columns which are common,

A: Order Number  B: Location  C: Date

 

What i want to establish is, If Order 5674 is entered in column A of "Ready for Billing " sheet and if i mistakenly entered the same order number in Column A of "Technical Issues"  it should color the cell in RED saying its a Duplicate.

( I cannot use any kind of 3rd party tools or software but i want to achieve this using conditional formatting or writing a module)

 

Thank you Upfront for your time. Your Ideas are greatly appreciated.

 

6 Replies
Highlighted

@Abhishek_gliot 

I created two sheets:

1. Ready for billing

2. Technical issues

 

On each sheet, I created a Named Table (use Ctrl+T or Home>Format as Table) with three columns:

1. Order number

2. Location

3. Date

 

The tables are named (respectively):

 

1. readyforbilling

2. technicalissues

 

FlexYourDAta_0-1594565037999.png

 

I place my cell in the first row of the technicalissues table in the Order number column, then I create a rule using Home>Conditional Formatting>New Rule, where I select "Use a formula to determine which cells to format".

The formula used in the rule is:

 

=NOT(ISERROR(VLOOKUP($A3,'Ready for billing'!$A:$A,1,FALSE)))

 

 

When I am editing the second parameter of the VLOOKUP formula, I click the column header of the first column (A) on the Ready for Billing sheet, such that it selects the entire column.

 

As you likely know, a conditional formatting formula must return either TRUE or FALSE.

 

This formula is looking for the order number from the current row in the technicalissues table in the order number column of the readyforbilling table. If it's found, VLOOKUP will return the order number from column 1 of the readyforbilling table, and so will not be an error. As such, the entire formula will return TRUE (because NOT inverts the return value of ISERROR, which would be FALSE).

 

If the order number is not found, VLOOKUP will return an error, ISERROR will return TRUE and NOT(ISERROR will return FALSE.

 

The net effect is that order numbers found in the readyforbilling table are formatted and those which are not, are not.

 

Because we are using Tables, the Applies To range will expand as each new row is added.

 

conditionalformatacrosssheets.gif

Highlighted

@OwenPrice

 

Thankyou so much for your efforts. Really appreciate it.

 

Abhishek_gliot_0-1594566897155.pngPicture 1: Technical Issues

Abhishek_gliot_1-1594566920128.pngPicture 2: Ready for billing

I exactly did what you have done ( Applied formula in order column, for Tech Issues sheet) and still, this is what i am seeing. Am i doing some thing wrong?

 

 

Highlighted
Hi, please add a screenshot of your conditional formatting rule and paste the exact formula in the rule into a code snippet.
Highlighted

 

Abhishek_gliot_0-1594571556745.png

=NOT(ISERROR(VLOOKUP($A3,'Ready for Billing'!$A$3:$A$5,1,FALSE)))

@OwenPrice 

Highlighted
Solution

@Abhishek_gliot 

 

Ok, since you are starting with data in the table, please go to the Conditional Formatting Rules Manager using Home>Conditional Formatting>Manage Rules.

 

Then, click the arrow to edit the Applies To range:

edit Applies To.png

 

Now put your cursor above the "order" column until you see a black arrow. When you see the black arrow, left-click so that the whole column is selected:

FlexYourDAta_0-1594572143619.png

 

Then click the down-arrow on the Conditional Formatting Rules Manager shown above.

 

Now select the rule and click Edit Rule.

Select the part of the formula that says $A3 and then click on the first row in the technicalissues table. For you, this will be changing it to $A2:

FlexYourDAta_1-1594572260087.png

When you first click the cell, it will show $A$2. You need to remove the second $. You can do this by deleting it, or by pressing F4 twice to cycle through relative and absolute references.

Next, highlight the part of the formula that gives the range to the Ready for Billing sheet:

FlexYourDAta_2-1594572355366.png

 

While that is highlighted, go to the Ready for Billing sheet and again, select the entirety of column A by clicking on the A column header:

FlexYourDAta_5-1594572656019.png

 

In the end, your rule formula will look something like this:

=NOT(ISERROR(VLOOKUP($A2,'Ready for billing'!$A:$A,1,FALSE)))

 
Now that you have deliberately selected the entire column for the lookup range, and repositioned your starting cell, you should have no problems with newly added rows.

Highlighted

Thankyou so much @OwenPrice

 

This worked perfect and i am able to apply the same formula vice versa on all sheets. 

 

Thankyou for your time and efforts. 

Have a great day.