Crosscheck code lives in source sheet

Man Chui

Hi There,

I need help, would like to see if possible to set up formula to crosscheck the code lives in different source sheet and get result YES/NO in Target sheet. I do have 9 source sheet & 1 target sheet.

All the sheets (total 10) that is having unique common code it can be the key value.

I tried to combine 9 sheets to a report and set up formula in target sheet, however, the report is unable to set as a reference sheet, so it's failed.

Is that possible combine different source sheet & work out one pivot table then set formula ?

a.) Don't know how to make one pivot with different worksheet.

b.) Pivot can be set as a reference sheet ?

Appreciate pls share any recommendation on how to get the result YES/NO in target sheet even we can do more step.

Andrée Starå

Hi @Man Chui

I hope you're well and safe!

You could use the COUNTIFS or a combination of INDEX/MATCH to check if the value is in sheet 1, and if it isn't, it checks sheet 2, and so on.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!