I am getting an Invalid operation for the below if statement
![bday2329](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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 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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
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 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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!