Creating a Skill Grid for tracking

So, I am trying to create something similar to what I have in Excel in Smartsheet where it looks to match two things and if both are present it returns true, if not then false. The problem is it is referencing another sheet for both things. For instance, If Paul's name exists, and he has a date in Column 2, then true. Is this possible in Smartsheet because I have tried multiple formulas and cannot seem to figure it out. Any help would be appreciated. I can send sheets to anyone to help them, help me. :)

Find more posts tagged with

Formulas

Accepted answers

Nick Korna

Hi @Paul Mangan,

This should be possible. The one thing I'm not sure on is for the date - it reads like you inputting a name onto a sheet (Sheet 1) which is then checked if present in a column on another sheet (Sheet 2). Is the date referred to on sheet 1 or 2?

If the name/date is both on sheet 2 and your data is something like this:

And in sheet 1 you input a name, then the presence of both the name and that a date is attached to it can be checked:

Where the formula in "Formula 1" is:

=IFERROR(IF(AND(MATCH(Name@row, {Sheet 2 Name}, 0) > 0, COUNTIFS({Sheet 2 Name}, Name@row, {Sheet 2 Date}, <>"") > 0), "True"), "False")

The { } indicates a cross sheet reference which you need to set up (can't just type/paste it in!) - if you're familiar with these then great, but if not then the below should help you see how to set these up:

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

https://www.youtube.com/watch?v=wdqGmdke4GA

If your date is also on sheet 1 and you're just checking the name is present on sheet 2 then your formula for the True/False check would be like this:

=IFERROR(IF(AND(MATCH(Name@row, {Sheet 2 Name}, 0) > 0, Date@row <> ""), "True", "False"), "False")

Sample output:

Hope this helps, but if I've misunderstood anything or you have any problems/questions then just ask!

All comments

Nick Korna

Hi @Paul Mangan,

If the name/date is both on sheet 2 and your data is something like this:

And in sheet 1 you input a name, then the presence of both the name and that a date is attached to it can be checked:

Where the formula in "Formula 1" is:

=IFERROR(IF(AND(MATCH(Name@row, {Sheet 2 Name}, 0) > 0, COUNTIFS({Sheet 2 Name}, Name@row, {Sheet 2 Date}, <>"") > 0), "True"), "False")

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

https://www.youtube.com/watch?v=wdqGmdke4GA

If your date is also on sheet 1 and you're just checking the name is present on sheet 2 then your formula for the True/False check would be like this:

=IFERROR(IF(AND(MATCH(Name@row, {Sheet 2 Name}, 0) > 0, Date@row <> ""), "True", "False"), "False")

Sample output:

Hope this helps, but if I've misunderstood anything or you have any problems/questions then just ask!

Paul Mangan

=VLOOKUP($[Primary Column]@row, {Operations}, 2, false)

so this is what i currently have and it is returning exactly what the cell is on the other page, but i want it to return "Yes", or "No".

=IF(VLOOKUP($[Primary Column]@row, {Operations}, 2), "Yes", "No")

When I put this formula it returns saying invalid data type.

Nick Korna

That's what a VLOOKUP does - you would have to change it to an IF statement based on the result of your lookup. How complex this is would depend on what determines if it is "Yes" or "No".

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, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.