Referencing another sheet for selective information

David Tompkins
David Tompkins ✭✭✭
edited 12/09/19 inFormulas and Functions

Any help with this would be appreciated.

I haven't been able to come up with a formula that would check a reference sheet for a value and pull the data from another column to populate the current form. I expect it would be a compound formula with an IF and maybe a INDEX, but I'm having difficulty narrowing it down. - Any thoughts would be much appreciated.

ReferenceData.png

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
You would need to leverage the Created Date column and a formula that will evaluate the time and add 1 to the date.<\/p>

=DATEONLY(Created@row) + IF(AND(FIND(\"P\", Created@row)> 0, VALUE(MID(Created@row, FIND(\" \", Created@row) + 1, FIND(\":\", Created@row) - (FIND(\" \", Created@row) + 1))) >= 4, VALUE(MID(Created@row, FIND(\" \", Created@row) + 1, FIND(\":\", Created@row) - (FIND(\" \", Created@row) + 1))) < 12), 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":227,"urlcode":"api-and-developers","name":"API and Developers"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":277,"urlcode":"Integrations","name":"Integrations"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":369,"urlcode":"bridge-by-smartsheet","name":"Bridge"}]},{"discussionID":108222,"type":"question","name":"Parent Status based on Children Statuses - Need help refining this formula","excerpt":"Hey Smartsheet Community! I have a beast of a formula that has probably gotten a bit away from me, but I need help refining this so it meets certain criteria per our stakeholders vision. Here's the formula: =IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), \"Complete\"), \"Complete\", IF(COUNT(CHILDREN()) = AND(COUNTIF(CHILDREN(),…","snippet":"Hey Smartsheet Community! I have a beast of a formula that has probably gotten a bit away from me, but I need help refining this so it meets certain criteria per our stakeholders…","categoryID":322,"dateInserted":"2023-07-27T20:58:59+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T12:20:53+00:00","insertUserID":146258,"insertUser":{"userID":146258,"name":"dhall","url":"https:\/\/community.smartsheet.com\/profile\/dhall","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T12:21:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":146258,"lastUser":{"userID":146258,"name":"dhall","url":"https:\/\/community.smartsheet.com\/profile\/dhall","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T12:21:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3381040192,"url":"https:\/\/community.smartsheet.com\/discussion\/108222\/parent-status-based-on-children-statuses-need-help-refining-this-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108222\/parent-status-based-on-children-statuses-need-help-refining-this-formula","format":"Rich","tagIDs":[219,254],"lastPost":{"discussionID":108222,"commentID":387762,"name":"Re: Parent Status based on Children Statuses - Need help refining this formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387762#Comment_387762","dateInserted":"2023-07-28T12:20:53+00:00","insertUserID":146258,"insertUser":{"userID":146258,"name":"dhall","url":"https:\/\/community.smartsheet.com\/profile\/dhall","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T12:21:16+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\/P3FFJGV2II8T\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T12:21:14+00:00","dateAnswered":"2023-07-28T00:26:52+00:00","acceptedAnswers":[{"commentID":387717,"body":"

Give this a try:<\/p>

=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = \"Canceled\"), \"Canceled\", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = \"Complete\", @cell = \"Canceled\", @cell = \"On Hold\")), \"Complete\", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = \"Not Started\"), \"Not Started\", IF(COUNTIFS(CHLDREN(), @cell = \"Upcoming\")> 0, \"Upcoming\", \"In Progress\"))))<\/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":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108218,"type":"question","name":"Help with \"AND\" formula","excerpt":"I'm using this formula where I want to generate the average complexity score for any projects in the year 2019 where the product is ESP. However, when I use this formula I'm getting an \"Invalid Data type\". I've confirmed all references are correct. Anyone have ideas? =AVERAGEIF(AND({Complexity Year}, \"2019\", {Product},…","snippet":"I'm using this formula where I want to generate the average complexity score for any projects in the year 2019 where the product is ESP. However, when I use this formula I'm…","categoryID":322,"dateInserted":"2023-07-27T20:23:56+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T12:47:12+00:00","insertUserID":156865,"insertUser":{"userID":156865,"name":"Katie S.","url":"https:\/\/community.smartsheet.com\/profile\/Katie%20S.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xWDX8YmJLfc!pKNXqRG2rHs!yB3MdbY6fmJ","dateLastActive":"2023-07-28T12:44:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":156865,"lastUser":{"userID":156865,"name":"Katie S.","url":"https:\/\/community.smartsheet.com\/profile\/Katie%20S.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xWDX8YmJLfc!pKNXqRG2rHs!yB3MdbY6fmJ","dateLastActive":"2023-07-28T12:44:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":37,"score":null,"hot":3381039668,"url":"https:\/\/community.smartsheet.com\/discussion\/108218\/help-with-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108218\/help-with-and-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108218,"commentID":387766,"name":"Re: Help with \"AND\" formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387766#Comment_387766","dateInserted":"2023-07-28T12:47:12+00:00","insertUserID":156865,"insertUser":{"userID":156865,"name":"Katie S.","url":"https:\/\/community.smartsheet.com\/profile\/Katie%20S.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xWDX8YmJLfc!pKNXqRG2rHs!yB3MdbY6fmJ","dateLastActive":"2023-07-28T12:44:57+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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T12:46:42+00:00","dateAnswered":"2023-07-27T22:53:31+00:00","acceptedAnswers":[{"commentID":387693,"body":"

AVERAGEIF and AND do not play well together. Try this one, it collects all values that meet your criteria and then averages them. I am assuming {Complexity Score New} is the range with the values you would like to average.<\/p>

=AVG(COLLECT({Complexity Score New}, {Complexity Year}, \"2019\", {Product}, \"ESP\"))<\/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":[]}">

Trending in Formulas and Functions