IF function

Josephine

I am trying to reference another sheet using IF but maybe its the wrong function.

First sheet is showing if an employee has completed an internal learning module that has 5 parts to it (these parts may change so do not want the formula to count the parts completed), the column has Yes when the module is completed. If that shows YES then in the other sheet I want to have "1" reflected in the column on the second sheet (that then affects another sheet, so want this sheet to automatically update when the module is passed on the first sheet).

I have tried =IF({IT Core Badge Range 2} = "Yes", 1, 0) and get INVALID OPERATION

Andrée Starå

Hi @Josephine

I hope you're well and safe!

Try something like this.

=IF(VLOOKUP([Full name]@row, {IT Core Badge Range 3}, 10, false) = "Yes", 1,0)

Did 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!

Darren Mullen

@Josephine You are comparing a range to a single value, that is why it is invalid. You would need a common unique key (such as employee ID or employee name) on the 2 sheets in order to check if the employee in sheet one has completed the module, then your second sheet could have the check box.

Josephine

Hi Darren,

Thanks for your reponse - what you are saying makes sense - I had been looking prior to asking question as was confused how it would work but couldn't find the answer. Still unclear though on the solution - I have now tried =VLOOKUP([Full name]@row, {HSE - LEVEL 1 Competency Badges Range 2},10, IF(Awarded@row = Yes, 1, 0))

Josephine

I'm getting closer with a bit of googling -

=IF(VLOOKUP([Full name]@row, {IT Core Badge Range 3}, 10, false) = "Yes", VLOOKUP([Full name]@row, {IT Core Badge Range 3}, 10, false), "0")

This gives me YES if YES in the other sheet and O if blank, I just need to get the YES to show 1

Andrée Starå

Hi @Josephine

I hope you're well and safe!

Try something like this.

=IF(VLOOKUP([Full name]@row, {IT Core Badge Range 3}, 10, false) = "Yes", 1,0)

Did 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!

Josephine

Thank you @Andrée Starå! That did it!

Andrée Starå

@Josephine

Excellent!

Happy to help!