I am getting an Invalid operation for the below if statement

bday2329

I have a weekly vaccine by facility sheet that I need to load information from the master sheet if the facil # = 1 and the Row wk = Prev week and if true use the value in Tier/age group

=IF(AND({Facil #} = 1, {Row Wk} = {Prev week}), {Tier/age group})

I am using Cross reference to check another sheet for the above

Facil # is blue arrow - this is a number assigned to each hospital facility via a vlookup

Row Wk is Green arrow

Prev week is black arrow

If both are true I want to set the cell value to Tier/age group red arrow

Thank you in advance for the help

Genevieve P.

Hi @bday2329

For a cross-sheet reference we'll use an INDEX(COLLECT formula instead of an IF(AND statement.

In an INDEX(COLLECT formula, you first list the column you want to have data returned from, then you list each column and criteria afterwards with commas between. See this other Community Post for an example.

Now, for the criteria you have where one cell should equal the other cell ({Row Wk} = {Prev week}), these cross-sheet ranges can't evaluate the row in the same way you can within it's own sheet, as the ranges look at the entire column. Instead, we can set up a helper column in the source sheet to indicate if this criteria is met on the row, then use the helper column in the formula.

For example, set up a checkbox column in this source sheet (you can hide it after the formula is applied). Then do a simple IF statement to say that if the cells in these two columns, for this row, are the same value, check the box:

=IF([Row Week]@row = [Prev Week]@row, 1, 0)

Now we can use this helper column in our INDEX(COLLECT function as one of the criteria. Try this instead:

=INDEX(COLLECT({Tier/age group}, {Facil #}, = 1, {Helper Column}, =1), 1)

Let me know if this makes sense and if it works for you!

Cheers,

Genevieve

bday2329

FYI for the above #INVALID DATA I have correct those but above formula still does not work gets same error

Genevieve P.

Hi @bday2329

For a cross-sheet reference we'll use an INDEX(COLLECT formula instead of an IF(AND statement.

In an INDEX(COLLECT formula, you first list the column you want to have data returned from, then you list each column and criteria afterwards with commas between. See this other Community Post for an example.

Now, for the criteria you have where one cell should equal the other cell ({Row Wk} = {Prev week}), these cross-sheet ranges can't evaluate the row in the same way you can within it's own sheet, as the ranges look at the entire column. Instead, we can set up a helper column in the source sheet to indicate if this criteria is met on the row, then use the helper column in the formula.

For example, set up a checkbox column in this source sheet (you can hide it after the formula is applied). Then do a simple IF statement to say that if the cells in these two columns, for this row, are the same value, check the box:

=IF([Row Week]@row = [Prev Week]@row, 1, 0)

Now we can use this helper column in our INDEX(COLLECT function as one of the criteria. Try this instead:

=INDEX(COLLECT({Tier/age group}, {Facil #}, = 1, {Helper Column}, =1), 1)

Let me know if this makes sense and if it works for you!

Cheers,

Genevieve