Formulas and Referencing Sheets
So with the new referencing sheets feature (at least I think it's new) is it possible to have one sheet that would be the master for all summaries and metric information rather than roll them up into extra rows at the top of each sheet?
Comments
-
J. Craig Williams ✭✭✭✭✭✭Yes.
That is one of the benefits of cross sheet referencing.
Craig
Help Article Resources
Categories
Try: =IF(OR(ISBLANK(Title@row), Title@row = \"Resource\"), 0, 3)<\/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":111329,"type":"question","name":"IF Statements and Summing Children","excerpt":"Hi there - I need a hand with a formula. I'm trying to do the following: Show the difference between Budgeted Amount and Quote Amount unless there is a value in the Actual Amount column, then I'd like to show the difference between the Budgeted Amount and the Actual Amount. But I only want to calculate those values if the…","snippet":"Hi there - I need a hand with a formula. I'm trying to do the following: Show the difference between Budgeted Amount and Quote Amount unless there is a value in the Actual Amount…","categoryID":322,"dateInserted":"2023-10-06T16:41:19+00:00","dateUpdated":null,"dateLastComment":"2023-10-06T19:01:55+00:00","insertUserID":159342,"insertUser":{"userID":159342,"name":"Beth Fantozzi 1","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20Fantozzi%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T19:37:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":159342,"lastUser":{"userID":159342,"name":"Beth Fantozzi 1","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20Fantozzi%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T19:37:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":32,"score":null,"hot":3393230594,"url":"https:\/\/community.smartsheet.com\/discussion\/111329\/if-statements-and-summing-children","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111329\/if-statements-and-summing-children","format":"Rich","lastPost":{"discussionID":111329,"commentID":398969,"name":"Re: IF Statements and Summing Children","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398969#Comment_398969","dateInserted":"2023-10-06T19:01:55+00:00","insertUserID":159342,"insertUser":{"userID":159342,"name":"Beth Fantozzi 1","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20Fantozzi%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T19:37:27+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\/8PZPFEHHKZ5S\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-06T19:01:13+00:00","dateAnswered":"2023-10-06T18:49:07+00:00","acceptedAnswers":[{"commentID":398967,"body":"
@Beth Fantozzi 1<\/a> <\/p> I got you - use this:<\/p> =IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), IF(AND(NOT(ISNUMBER([Quote Amount]@row)), NOT(ISNUMBER([Actual Amount]@row))), 0, IF(ISNUMBER([Actual Amount]@row), [Budgeted Amount]@row - [Actual Amount]@row, [Budgeted Amount]@row - [Quote Amount]@row)))<\/p><\/div><\/div> It ends up looking like this:<\/p> <\/p> _____________________________________________________________________________________________<\/p> 👨🏼💻 Dan Palenchar | <\/strong>School of Sheets Solutions Consulting<\/strong><\/a> | Smartsheet Aligned Gold Partner<\/strong><\/p> If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and\/or ❤️Awesome).<\/em><\/p> 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com\/workwithus<\/a><\/p> ▶️ Smartsheet Tutorial Videos: schoolofsheets.com\/youtube<\/a><\/p>![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/5U7E27I89YBF\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
