VLOOKUP for multiple options influencing Gantt chart
I have a sheet that uses Gantt chart data to show assignments on a calendar.
The persons assigned are all in a "Name" column that is a drop down with all the employee names on them. Next to that are start and end date columns that then show a bar range in calendar view.
The employee's name will only appear in the bar in calendar view if the name is in the Primary Column.
The Primary Column can only be text, and NOT a drop down list.
I have been successful in using VLOOKUP so that when the drop down name is chosen in the Name column, that name populates into the Primary Column.
My problem, is that I cannot figure out how to do that for multiple choices. (For example, if in my drop down I have employees Larry, Moe, and Curly, my VLOOKUP works for only one of those names.) Is there a way to use VLOOKUP so it will return the correct name for any employee chosen?
Comments
-
Paul Newcome ✭✭✭✭✭✭Is the dropdown list on the same sheet? Are you able to provide some screenshots?
thinkspi.com
-
hcosta52671 ✭✭✭✭
Yes. The drop down is on the same sheet. It's the name column in the screenshot.
The ID column is the primary that I want populated with my choice from the drop down.
My goal is to be able to easily build an on-call calendar. The drop down populating the primary column would cut down on typing.
The only way I've gotten it to work, as the screen shot shows, is that each row has the specific formula identifying that person.
So, in row 1, if I chose "Nick" or "Kira" it would give me a #NO MATCH error in the ID (primary) column.
-
Paul Newcome ✭✭✭✭✭✭
Would it work to just use a basic
to pull the name from the Name column into the Primary Column? What is the reason for using the VLOOKUP?
thinkspi.com
-
hcosta52671 ✭✭✭✭
Holy cow!!
That did exactly what I wanted it to do!
I was using VLOOKUP because that was the only formula that I could see that would do what I wanted to.
Thank you very much! You've earned your gold star for the day!
-
Paul Newcome ✭✭✭✭✭✭
Happy to help! There are actually a handful of formulas that can be used to pull data.
There is a template in the solution center that contains examples of all of the different functions. It is totally interactive, and if you accidentally mess something up, you can delete the sheet and download a fresh template. See the snippet below.
thinkspi.com
Help Article Resources
Categories
If that's true, this should do it:<\/p>
=IF(AND([Employment Status]@row = \"Temporary Replacement\", [In AOP]@row = 1), 1, 0)<\/p>
Not sure why you're using \"FIND.\" Are you trying to get a summary of ALL rows that have both \"Temporary Replacement\" and the In AOP box checked? If so, you could create a column that uses the formula I provided, then use the worksheet summary flyout to place a COUNTIF formula for that summary.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":103924,"type":"question","name":"Help with sum formula counting blank cells as zero","excerpt":"Hiya again Smarthsheet community! Hoping someone much smarter than me can help with this formula issue. I have a formula that sums six cells that I want to sum and divide by 30. This then generates a related IF formula to return a text value in another cell (High, Medium, Low, None). All is generally working fine, but it's…","categoryID":322,"dateInserted":"2023-04-12T14:18:13+00:00","dateUpdated":null,"dateLastComment":"2023-04-12T16:20:57+00:00","insertUserID":160148,"insertUser":{"userID":160148,"name":"JBYYC","url":"https:\/\/community.smartsheet.com\/profile\/JBYYC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nH14DQG3YON84.png","dateLastActive":"2023-04-12T15:24:45+00:00","banned":0,"punished":0,"private":true,"label":"✭✭"},"updateUserID":null,"lastUserID":160148,"lastUser":{"userID":160148,"name":"JBYYC","url":"https:\/\/community.smartsheet.com\/profile\/JBYYC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nH14DQG3YON84.png","dateLastActive":"2023-04-12T15:24:45+00:00","banned":0,"punished":0,"private":true,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":32,"score":null,"hot":3362626750,"url":"https:\/\/community.smartsheet.com\/discussion\/103924\/help-with-sum-formula-counting-blank-cells-as-zero","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/103924\/help-with-sum-formula-counting-blank-cells-as-zero","format":"Rich","lastPost":{"discussionID":103924,"commentID":371438,"name":"Re: Help with sum formula counting blank cells as zero","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/371438#Comment_371438","dateInserted":"2023-04-12T16:20:57+00:00","insertUserID":160148,"insertUser":{"userID":160148,"name":"JBYYC","url":"https:\/\/community.smartsheet.com\/profile\/JBYYC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nH14DQG3YON84.png","dateLastActive":"2023-04-12T15:24:45+00:00","banned":0,"punished":0,"private":true,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-04-12T18:11:52+00:00","dateAnswered":"2023-04-12T15:02:58+00:00","acceptedAnswers":[{"commentID":371401,"body":"
Hello JBYYC,<\/p>
I think I have something that will help. If I am understanding your question correctly, you are wanting the cell that has the IF formula in it to be blank if the helper cells are blank. If this is not the case, please let me know. If that is the case, then try putting <\/p>
=IF(AND(ISBLANK(drhelper@row), ISBLANK(orghelper@row), ISBLANK(sphelper@row), ISBLANK(caphelper@row), ISBLANK(bhelper@row), ISBLANK(mhelper@row)), \"//www.santa-greenland.com/community/discussion/comment/\", (continue with your dependent formula)<\/p>
See if this helps you out.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":103912,"type":"question","name":"COUNTIFS with OR","excerpt":"Hi all I think this should be simple formula, but I just can't get it work. Any help would be gratefully received. I would like to count the times both possibilities of \"Action\" and \"Forthcoming Meeting\" appear in my column 'Type'. This formula isn't working: =COUNTIFS(Type:Type, \"Action\", OR (@cell = \"Forthcoming…","categoryID":322,"dateInserted":"2023-04-12T11:31:32+00:00","dateUpdated":null,"dateLastComment":"2023-04-12T13:48:27+00:00","insertUserID":160404,"insertUser":{"userID":160404,"name":"Lisa Bowley","url":"https:\/\/community.smartsheet.com\/profile\/Lisa%20Bowley","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!w3tn_webgzI!nenmHw-9kbI!htMjdRaigqg","dateLastActive":"2023-04-12T13:58:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-04-13T00:57:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":35,"score":null,"hot":3362608199,"url":"https:\/\/community.smartsheet.com\/discussion\/103912\/countifs-with-or","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/103912\/countifs-with-or","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":103912,"commentID":371366,"name":"Re: COUNTIFS with OR","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/371366#Comment_371366","dateInserted":"2023-04-12T13:48:27+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-04-13T00:57:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-04-12T13:29:22+00:00","dateAnswered":"2023-04-12T11:59:51+00:00","acceptedAnswers":[{"commentID":371325,"body":"
Hi @Lisa Bowley<\/a> <\/p> I hope you're well and safe!<\/p> Try something like this.<\/p> =COUNTIFS(Type:Type, OR(CONTAINS(\"Action\", @cell), CONTAINS(\"Forthcoming Meetings\", @cell)))<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"},{"commentID":371358,"body":" Hi Andree! Thanks for that, works perfectly, as usual :-)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
