Cross referencing same range across multiple sheets

I am using one sheet, call it Program Data, as my source data for piping into my many Project Sheets. The Project Sheets have a Project ID in one cell that determines which line of the Program Data sheet to call out (e.g. Start Date, Team Lead 1, Team Lead 2, Team Lead 3, ...). I can't find a way to link each cell without manually defining the ranges for every cell. Is there a way to define an absolute range in a cross-reference that spans different sheets? Or simply a way to cross-reference a range or another entire sheet without having to click through the range every time?

In other words, if I define the range {Program Data Start Date} or {Program Data Project ID} while referencing from Project Sheet X, that range can successfully be referenced in Project Sheet X but {Program Data Start Date} or {Program Data Project ID} are not defined in Project Sheet Y. I would have to manually click 'Reference another sheet' and define the ranges for every cell in Project Sheet Y and all additional sheets I wanted to autopopulate from Program Data.

My formula is a simple Index Match and works just fine.

If not possible, then I would like to request this functionality, as it is a base functionality for any automation in my opinion.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    It is not possible to transfer cross sheet references from one sheet to another already existing sheet. Feel free to submit an enhancement request using the "Product Ideas" tab at the top of this sheet.


    If you have created these cross sheet references and then "save as new" the project sheet, the cross sheet references will copy over as well. It is just transferring them to an already existing sheet that isn't possible.

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi@Gary S

    I hope you're well and safe!

    Not sure I follow!

    You'd have to create each range for each sheet except if you create a master sheet and then copy it for each project.

    Make sense?

    I hope that helps!

    Be safe, and have a fantastic weekend!

    Best,

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    了我的帖子(s)帮助或回答哟ur question or solve your problem? Please support the Community bymarking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    It is not possible to transfer cross sheet references from one sheet to another already existing sheet. Feel free to submit an enhancement request using the "Product Ideas" tab at the top of this sheet.


    If you have created these cross sheet references and then "save as new" the project sheet, the cross sheet references will copy over as well. It is just transferring them to an already existing sheet that isn't possible.

Hi @David Sprague<\/a>, <\/p>

I hope you're well and safe!<\/p>

There's a known issue at the moment.<\/p>

I'll get back to the post if I can find the thread.<\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/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>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":108880,"type":"question","name":"Recover a Row that was accidentally moved then deleted","excerpt":"I was attempting to copy a row from sheet 1 to sheet 2, but I accidentally moved the row then deleted it, but then realized belatedly that the original (now deleted) data in sheet 1 had been moved. Apparently one can not simply use the \"undo action\" arrow after moving something--the option is grayed out as in not…","snippet":"I was attempting to copy a row from sheet 1 to sheet 2, but I accidentally moved the row then deleted it, but then realized belatedly that the original (now deleted) data in sheet…","categoryID":321,"dateInserted":"2023-08-11T22:27:56+00:00","dateUpdated":null,"dateLastComment":"2023-08-12T05:12:42+00:00","insertUserID":165013,"insertUser":{"userID":165013,"name":"GlennJo","title":"Riparian Stewardship Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/GlennJo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-12T03:46:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":165013,"lastUser":{"userID":165013,"name":"GlennJo","title":"Riparian Stewardship Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/GlennJo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-12T03:46:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":32,"score":null,"hot":3383611238,"url":"https:\/\/community.smartsheet.com\/discussion\/108880\/recover-a-row-that-was-accidentally-moved-then-deleted","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108880\/recover-a-row-that-was-accidentally-moved-then-deleted","format":"Rich","lastPost":{"discussionID":108880,"commentID":390348,"name":"Re: Recover a Row that was accidentally moved then deleted","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390348#Comment_390348","dateInserted":"2023-08-12T05:12:42+00:00","insertUserID":165013,"insertUser":{"userID":165013,"name":"GlennJo","title":"Riparian Stewardship Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/GlennJo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-12T03:46:04+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-12T03:46:02+00:00","dateAnswered":"2023-08-11T23:45:38+00:00","acceptedAnswers":[{"commentID":390342,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/108880\/recover-a-row-that-was-accidentally-moved-then-deleted\n <\/a>\n<\/div>\n

Hey @GlennJo<\/a>,<\/p>

the sheet should be available in the owner of the sheet, deleted files. If it is specifically a row of data that was deleted, you can try checking the activity log to see if it shows the removed data there.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":108822,"type":"question","name":"Activity Log | Rows Deleted Filter","excerpt":"I've got a conundrum on my hands where a record has gone missing. In the Activity Log, I'm able to find the record of when it was created. However, there is nothing I'm seeing indicating that it was deleted (even though the Activity Log doesn't have a specific filter for this, I traced through the log for the past 11 days…","snippet":"I've got a conundrum on my hands where a record has gone missing. In the Activity Log, I'm able to find the record of when it was created. However, there is nothing I'm seeing…","categoryID":321,"dateInserted":"2023-08-10T21:46:08+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T22:01:16+00:00","insertUserID":121055,"insertUser":{"userID":121055,"name":"Jake Gustafson","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jake%20Gustafson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!-lnW91tuGTw!bO9UmyNQSXQ!WUYBUm0HF6t","dateLastActive":"2023-08-11T20:29:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":121055,"lastUser":{"userID":121055,"name":"Jake Gustafson","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jake%20Gustafson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!-lnW91tuGTw!bO9UmyNQSXQ!WUYBUm0HF6t","dateLastActive":"2023-08-11T20:29:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":39,"score":null,"hot":3383410044,"url":"https:\/\/community.smartsheet.com\/discussion\/108822\/activity-log-rows-deleted-filter","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108822\/activity-log-rows-deleted-filter","format":"Rich","tagIDs":[447],"lastPost":{"discussionID":108822,"commentID":390131,"name":"Re: Activity Log | Rows Deleted Filter","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390131#Comment_390131","dateInserted":"2023-08-10T22:01:16+00:00","insertUserID":121055,"insertUser":{"userID":121055,"name":"Jake Gustafson","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jake%20Gustafson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!-lnW91tuGTw!bO9UmyNQSXQ!WUYBUm0HF6t","dateLastActive":"2023-08-11T20:29:49+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/SIFFD1TSN3Q6\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T22:01:20+00:00","dateAnswered":"2023-08-10T22:01:16+00:00","acceptedAnswers":[{"commentID":390131,"body":"

Ok, I found it. Classic case of submit something for help, then find the answer. I think what may have happened previously, is that I turned on all the filters, but since there isn't one for a Deleted Row, maybe the Deleted Row was hidden. When I came back, I didn't click anything on the filters for the Activity Log and when scrolling through looking for the 'magic' delete word, I found the record I was looking for.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":447,"urlcode":"activity-log","name":"Activity Log"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=321&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":4941,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Smartsheet Basics