What formula do I use for my needs?

I have 2 sheets (see screenshots) and what I need is to have the Associate Name from the Associate List sheet copied to a column in the Permissions sheet but only if certain criteria is meet.

If there is a date in the Yard Access AND GTDR Expiration columns, I need the Associate name listed in the GTDR Certified column on the Permissions sheet. Likewise, if there is only a date in the Yard Access and the GTDR is blank, then the Associate name would go in the Yard Access Only column. I have tried so many different combinations of IF formulas and even tried one with INDEX and I'm getting #UNPARSEABLE every time.

If I use this formula on the same sheet in a new column I get results but, if I try to use the same formula in the Permissions sheet and change it to reference the other page - { } instead of [ ] - I still get UNPARSEABLE

=IF(AND(NOT(ISBLANK([Yard Access Expiration]@row)), ISBLANK([GTDR Expiration]@row)), [Associate Name]@row, "")

Any help is GREATLY appreciated!!!!

Find more posts tagged with

IF(

row

isnotblank formula

INDEX

Accepted answers

Paul Newcome

The number column would go on the Permissions sheet.

All comments

Melissa Yamada

Hello @Lisa McMunn

Try this:

Yard Access Only column:

=INDEX(COLLECT({Associate Name},{Yard Access Expiration},@cell<>"",{GTDR Expiration},@cell=""),1)

GTDR Certified:

=INDEX(COLLECT({Associate Name},{Yard Access Expiration},@cell<>"",{GTDR Expiration},@cell<>""),1)

Paul Newcome

Can you provide a screenshot of the expected output manually entered so we know exactly what you are wanting to accomplish?

Lisa McMunn

@Paul Newcome I would like it to have this result. If I were to add or delete associates it will update these lists for my management

Lisa McMunn

@Melissa Yamada Those did work but are only pulling the first instance of a matching criteria - Row 1 & 2 - it isn't pulling the rest of the data on the sheet

Paul Newcome

You will need to insert a text/number column (called "Number" in this example) with numbers manually entered starting with 1 and extending through the maximum number you think you will need (plus a suggested few extra for a buffer).

Then you would adjust your formulas so that instead of the 1 you have Number@row.

=IFERROR(INDEX(COLLECT(……………..), Number@row), "")

Lisa McMunn

https://community.smartsheet.com/discussion/comment/477087#Comment_477087

I'm not sure about what you are suggesting. Will this new column be on the sheet with the dates and Associates or on the Permissions sheet where I want the names listed?

Paul Newcome

The number column would go on the Permissions sheet.

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.