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. :)
Best Answer
-
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:
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!
Answers
-
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:
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!
-
=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.
-
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".
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!