I am getting an Invalid operation for the below if statement
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
Best Answer

Hi @bday2329
For a crosssheet 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 crosssheet 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
Answers

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

Hi @bday2329
For a crosssheet 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 crosssheet 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
Help Article Resources
Categories
Check out the Formula Handbook template!