Formula returning 'Invalid Operation'

PeggyLang

Hello,

I'm having trouble with my formula and can't quite figure out how to fix it.

=IF(AND([LookUp Value]10 = "Y", {April THD Tech on Call WK1} = 1), JOIN(COLLECT({April THD Tech on Call Name}, {April THD Tech on Call WK1 TOC}, HAS(@cell, [LookUp Value]2))))

Anything obvious that I am missing?

Paul Newcome

I actually missed a closing parenthesis. Give this a try instead:

=IF(AND([LookUp Value]10 = "Y", INDEX(COLLECT({April THD Tech on Call WK1}, {April THD Tech on Call WK1 TOC}, HAS(@cell, [LookUp Value]2)), 1) = 1), JOIN(COLLECT({April THD Tech on Call Name}, {April THD Tech on Call WK1 TOC}, HAS(@cell, [LookUp Value]2))))

Paul Newcome

What exactly are you trying to accomplish with this formula?

PeggyLang

I have a worksheet listing all technicians and columns for each week in April.

Lookup wants to find the technician on call for the specific store # and the appropriate date.

In this instance I'm wanting to return the Name 'Loc'

Paul Newcome

The checkbox range/criteria set would go inside of the COLLECT function.

You will also need to change the [APR WK1 TOC] column to be a multi-select dropdown and make all of the selections appropriately (no commas) for the HAS function to work.

PeggyLang

@Paul Newcome i'm not sure I understand 'the checkbox range/criteria set would go inside of the COLLECT function'.

This will be a larger nested IF statement, whereby if 'April THD Tech on Call WK1'=0, then I need to evaluate whether 'April THD Tech on Call WK2' = 1 and so on.

Paul Newcome

In that case you need to use an INDEX/MATCH or an INDEX/COLLECT to bring the checkbox for that location/ week number over within the AND function.

=IF(AND([LookUp Value]10 = "Y", INDEX(COLLECT({April THD Tech on Call WK1}, {April THD Tech on Call WK1 TOC}, HAS(@cell, [LookUp Value]2), 1) = 1), JOIN(COLLECT({April THD Tech on Call Name}, {April THD Tech on Call WK1 TOC}, HAS(@cell, [LookUp Value]2))))

PeggyLang

@Paul Newcome

I'm not quite understanding the formula, wondering if you can explain it in laymans' terms for me?

I think it is saying;

If [LookUp Value]10 = "Y" AND the collection of { . . . on Call WK1} + { . . . on Call WK1 TOC} when Store # is present then JOIN the collection of { . . . on Call Name} + { . . . on Call WK1 TOC} when Store # is present?

Is my understanding correct?

Why ' . . . HAS(@cell, [LookUp Value]2),1) = 1), . . . ~ what is the ',1)' for/indicating.

In any case I am returned with '#INCORRECT ARGUMENT' as well as my formula ends up with 5 x ')' at the end

Paul Newcome

I actually missed a closing parenthesis. Give this a try instead:

=IF(AND([LookUp Value]10 = "Y", INDEX(COLLECT({April THD Tech on Call WK1}, {April THD Tech on Call WK1 TOC}, HAS(@cell, [LookUp Value]2)), 1) = 1), JOIN(COLLECT({April THD Tech on Call Name}, {April THD Tech on Call WK1 TOC}, HAS(@cell, [LookUp Value]2))))

PeggyLang

@Paul Newcome THANK YOU!!!! My hero for the day.

How did you learn so much about formulas? AND you are always monitoring the sight for peoples' hurdles to help with. It's just an awesome community.

The above formula is the start. I need to build a large nested IF formula which will check for check marks in columns 'WK1', 'WK2', 'WK3' . . . 'WK8' (two months). I'm hoping I can manage to figure that one out on my own now that the 'heavy lifting' has been done.