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?
-
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
=Name@row
to pull the name from the Name column into the Primary Column? What is the reason for using the VLOOKUP?
-
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.
Help Article Resources
Categories
Replace [Employee] if your column has a difference name. This will essentially generate a list of distinct entries and then count them.<\/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"}]},{"discussionID":109307,"type":"question","name":"Show approval status based on # of approvals vs. denials","excerpt":"I'm looking for a formula that will show \"Yes\", \"Hold\", or \"No\" based on # of approvals. There are 6 total approvals needed, if all 6 approve, then the Approved column would show \"Yes\". If there is 1 or more denials, then the Approved column would show \"No\". If the total of the columns don't equal to 6, then the Approved…","snippet":"I'm looking for a formula that will show \"Yes\", \"Hold\", or \"No\" based on # of approvals. There are 6 total approvals needed, if all 6 approve, then the Approved column would show…","categoryID":322,"dateInserted":"2023-08-22T21:20:12+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T22:04:40+00:00","insertUserID":96646,"insertUser":{"userID":96646,"name":"@SPark","url":"https:\/\/community.smartsheet.com\/profile\/%40SPark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!co_Wh_2S-n4!ZAPZGbmgZrw!TRZYwfjvFdi","dateLastActive":"2023-08-22T22:57:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":96646,"lastUser":{"userID":96646,"name":"@SPark","url":"https:\/\/community.smartsheet.com\/profile\/%40SPark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!co_Wh_2S-n4!ZAPZGbmgZrw!TRZYwfjvFdi","dateLastActive":"2023-08-22T22:57:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":28,"score":null,"hot":3385482292,"url":"https:\/\/community.smartsheet.com\/discussion\/109307\/show-approval-status-based-on-of-approvals-vs-denials","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109307\/show-approval-status-based-on-of-approvals-vs-denials","format":"Rich","lastPost":{"discussionID":109307,"commentID":392022,"name":"Re: Show approval status based on # of approvals vs. denials","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392022#Comment_392022","dateInserted":"2023-08-22T22:04:40+00:00","insertUserID":96646,"insertUser":{"userID":96646,"name":"@SPark","url":"https:\/\/community.smartsheet.com\/profile\/%40SPark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!co_Wh_2S-n4!ZAPZGbmgZrw!TRZYwfjvFdi","dateLastActive":"2023-08-22T22:57:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/AJ5OBZXXQRAU\/2820-29-intake-smartsheet-com-2023-08-22-14-16-38.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"(20) Intake - Smartsheet.com 2023-08-22 14-16-38.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-22T22:04:29+00:00","dateAnswered":"2023-08-22T21:36:22+00:00","acceptedAnswers":[{"commentID":392011,"body":"
Something like this: =IF([Approval Count]@row = 6, \"Yes\", IF([Denied Count]@row >= 1, \"No\", \"Hold\"))<\/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":109287,"type":"question","name":"Formula help","excerpt":"=IF([WD Schematic End]@row = \"n\/a\", 0, IF([WD Schematic End]@row > [STR Schematic End]@row, 1, 0)) Above formula meant to trigger a red flag if the WD & STR Schematic End dates do not match. The formula I wrote is pulling back 'invalid' ... I am missing something and cannot figure out what!","snippet":"=IF([WD Schematic End]@row = \"n\/a\", 0, IF([WD Schematic End]@row > [STR Schematic End]@row, 1, 0)) Above formula meant to trigger a red flag if the WD & STR Schematic End dates do…","categoryID":322,"dateInserted":"2023-08-22T17:45:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T18:30:00+00:00","insertUserID":157887,"insertUser":{"userID":157887,"name":"holli.nunn","url":"https:\/\/community.smartsheet.com\/profile\/holli.nunn","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T18:29:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":157887,"lastUser":{"userID":157887,"name":"holli.nunn","url":"https:\/\/community.smartsheet.com\/profile\/holli.nunn","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T18:29:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3385456532,"url":"https:\/\/community.smartsheet.com\/discussion\/109287\/formula-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109287\/formula-help","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109287,"commentID":391947,"name":"Re: Formula help","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391947#Comment_391947","dateInserted":"2023-08-22T18:30:00+00:00","insertUserID":157887,"insertUser":{"userID":157887,"name":"holli.nunn","url":"https:\/\/community.smartsheet.com\/profile\/holli.nunn","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T18:29:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/DLAV2H53BW86\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-22T18:29:34+00:00","dateAnswered":"2023-08-22T18:25:02+00:00","acceptedAnswers":[{"commentID":391943,"body":"
Give this a try:<\/p>
=IF([WD Schematic End]@row = \"n\/a\", 0, IF([WD Schematic End]@row <> [STR Schematic End]@row, 1, 0))<\/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"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
