Check if parent row has corresponding values

%3CLINGO-SUB%20id%3D%22lingo-sub-1403886%22%20slang%3D%22en-US%22%3ECheck%20if%20parent%20row%20has%20corresponding%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1403886%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20have%20a%20table%20in%20an%20Excel%202016%20spreadsheet%20that%20has%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Ehierarchical%20rows%3C%2FSTRONG%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22a1.png%22%20style%3D%22width%3A%20467px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F193167iF0669DF14E5985B6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22a1.png%22%20alt%3D%22a1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E(Here's%20an%20Excel-friendly%20version%20that%20can%20be%20copied%2Fpasted%20into%20a%20spreadsheet.)%3C%2FEM%3E%3C%2FP%3E%3CTABLE%20width%3D%22444px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EID%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2251px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EPARENT%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2285px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EUseWith1%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2265px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EUseWith2%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EUseWith3%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22133px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EUseWith-CheckParent%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3E10%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2251px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2285px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EWORKORDER%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2265px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3ESR%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22133px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3E20%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2251px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3E10%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2285px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3ESR%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2265px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22133px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3E30%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2251px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3E10%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2285px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EWORKORDER%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2265px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EJOBPLAN%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22133px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3E40%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2251px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3E10%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2285px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EWORKORDER%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2265px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22133px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3E50%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2251px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3E40%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2285px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EWORKORDER%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2265px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3EJOBPLAN%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3ETOOLITEM%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22133px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQuestion%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20column%20F%2C%20I%20want%20to%20flag%20child%20rows%20where%20the%20values%20in%20C%2C%20D%2C%20or%20E%20are%20missing%20from%20the%20parent%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22a2.png%22%20style%3D%22width%3A%20788px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F193168i690DD53DC94A03BD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22a2.png%22%20alt%3D%22a2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20do%20this%20in%20Excel%202016%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1403886%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1404674%22%20slang%3D%22en-US%22%3ERe%3A%20Check%20if%20parent%20row%20has%20corresponding%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1404674%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F673242%22%20target%3D%22_blank%22%3E%40UserBlue1973%3C%2FA%3E%26nbsp%3Brefer%20attached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1405694%22%20slang%3D%22en-US%22%3ERe%3A%20Check%20if%20parent%20row%20has%20corresponding%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1405694%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F662966%22%20target%3D%22_blank%22%3E%40bhushan_z%3C%2FA%3E%26nbsp%3BThanks%2C%20however%2C%20I%20can't%20download%20the%20.xlsm%20file%20due%20to%20security%20reasons.%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20a%20reason%20why%20the%20file%20needs%20to%20be%20a%20.xlsm%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1405790%22%20slang%3D%22en-US%22%3ERe%3A%20Check%20if%20parent%20row%20has%20corresponding%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1405790%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F673242%22%20target%3D%22_blank%22%3E%40UserBlue1973%3C%2FA%3E%26nbsp%3B%3A%20even%20i%20m%20not%20sure%20it%20was%20saved%20as%20.xlsm%2C%20hahaha%3C%2FP%3E%3CP%3Ehere%20is%20new%20file%20in%20.xlsx%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20refer%20below%20formula%20just%20for%20your%20reference.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(B2%3D%22%22%2C%22%22%2CIF(AND(D2%3D%22%22%2CE2%3D%22%22)%2C%22%22%2CIF(D2%3D%22%22%2C%22ERROR%5B%22%26amp%3BE2%26amp%3B%22%20is%20missing%20from%20parent%20%23%22%26amp%3BB2%26amp%3B%22%5D%22%2CIF(E2%3D%22%22%2C%22ERROR%5B%22%26amp%3BD2%26amp%3B%22%20is%20missing%20from%20parent%20%23%22%26amp%3BB2%26amp%3B%22%5D%22%2C%22ERROR%5B%22%26amp%3BD2%26amp%3B%22%20and%20%22%26amp%3BE2%26amp%3B%22%20is%20missing%20from%20parent%20%23%22%26amp%3BB2%26amp%3B%22%5D%22))))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1408953%22%20slang%3D%22en-US%22%3ERe%3A%20Check%20if%20parent%20row%20has%20corresponding%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1408953%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F662966%22%20target%3D%22_blank%22%3E%40bhushan_z%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1409071%22%20slang%3D%22en-US%22%3ERe%3A%20Check%20if%20parent%20row%20has%20corresponding%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1409071%22%20slang%3D%22en-US%22%3EGlad%20to%20help%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3EIf%20it%20works%2C%20pls%20marks%20above%20reply%20as%20%22Official%20Answer%22%20and%20we%20can%20close%20this%20thread.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have a table in an Excel 2016 spreadsheet that has hierarchical rows:

 

a1.png

 

(Here's an Excel-friendly version that can be copied/pasted into a spreadsheet.)

IDPARENTUseWith1UseWith2UseWith3UseWith-CheckParent
10 WORKORDERSR  
2010SR   
3010WORKORDERJOBPLAN  
4010WORKORDER   
5040WORKORDERJOBPLANTOOLITEM 

 

Question:

 

In column F, I want to flag child rows where the values in C, D, or E are missing from the parent row.

 

Example:

a2.png

 

Is there a way to do this in Excel 2016?

5 Replies
Highlighted

@UserBlue1973 refer attached file

Highlighted

@bhushan_z Thanks, however, I can't download the .xlsm file due to security reasons.

Is there a reason why the file needs to be a .xlsm?

Highlighted

@UserBlue1973 : even i m not sure it was saved as .xlsm, hahaha

here is new file in .xlsx 

Also refer below formula just for your reference.

=IF(B2="","",IF(AND(D2="",E2=""),"",IF(D2="","ERROR["&E2&" is missing from parent #"&B2&"]",IF(E2="","ERROR["&D2&" is missing from parent #"&B2&"]","ERROR["&D2&" and "&E2&" is missing from parent #"&B2&"]"))))
Highlighted
Highlighted
Glad to help
If it works, pls marks above reply as "Official Answer" and we can close this thread.