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!

Find more posts tagged with

Accepted answers

All comments

SteyJ

https://community.smartsheet.com/discussion/109809/if-and-formula-referencing-another-sheet

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,

MichaelTCA

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")

Yvette Moreland

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.