IF AND Formula referencing another sheet
I have about 250 locations that need to submit a monthly report. I need to show which locations have and have not submitted their report for each month, and a need a year tracking.
I have a SUBMISSION sheet where their submissions populate. They select the month for which they are submitting from a dropdown.
I have a REQUIRED sheet which lists the locations who need the report and where we are manually tracking each month.
I want a formula on the REQUIRED sheet that will enter 'yes' in the month column if there is a submission for that month on the SUBMISSION sheet.
I cannot get the structure correct, so showing on three separate lines here what I want the formula to do:
=IF{SUBMISSION Range 1}, "AAA"
ANDIF {SUBMISSION Range 2}, "08-August"
then enter "yes"
Any help would be appreciated!
Answers
-
Hi @Yvette Moreland, Here are two options:
=IF(AND(HAS("AAA", {SUBMISSION Range 1}), HAS("08-August", {SUBMISSION Range 2})), "Yes", "")
=IF(AND(CONTAINS("AAA", {SUBMISSION Range 1}), CONTAINS("08-August", {SUBMISSION Range 2})), "Yes", "")
Let me know if this helps,
Sincerely,
Jacob Stey
-
Hello @Yvette Moreland
You can use a helper column in the SUBMISSION sheet that determines whether the statement you're looking for is true or false.
=IF(AND(SUBMISSION1@row="AAA",SUBMISSION2@row="08-August)=TRUE,"Yes","No")
Then use a lookup function and reference the helper column from the REQUIRED sheet.
=INDEX(COLLECT({Helper Column}, {SUBMISSION Range 1}, @cell = "AAA", {Submission Range 2}, @cell = "08-August"), 1)
Or add just a single column to the REQUIRED sheet, here's another way to do it. It really depends on how many places you want to reference the data and how easily you are able to reference that data as a whole.
=IF(AND(INDEX({Submission 1}, MATCH([Submission 1]@row, {Submission 1}, 0)) = "AAA", INDEX({Submission 2}, MATCH([Submission 2]@row, {Submission 2}, 0)) = "08-August"), "Yes", "No")
-
Thank you MichaelTCA and SteyJ.
All great options that worked.
I went with the helper column on the SUBMISSION sheet because that gave me a way to push the data to another sheet and build a more streamlined report for stakeholders.
You have saved me a lot of time! Thanks again.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!