Linking to other cells

galyo
edited 12/09/19 in Smartsheet Basics

Hey,

So we have 2 sheets. New Projects sheet and Tasks sheet.

The New Projects one have 2 column. ID and name of the project, whenever we get a new project we insert the name and ID manually.

I want the Tasks sheet to have those column as well (ID and name) and I want it to be dynamic. Meaning we add on Projects sheet, it automatically appear in the Tasks sheet.

On excel, i'll just use: ='NewProject'!A1 and 'NewProject'!B1

and then just 'stretch' the formula down, so whenever I add something in NewProject, it will be added to Tasks, and if there's nothing, it will just stay blank.

So in excel i would get:

='NewProject'!A1

='NewProject'!A2

='NewProject'!A3

='NewProject'!A4

etc.

 

But here, it seems like I can't stretch the formula no matter what... How can I do it?

Thanks!

Comments

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 04/11/18

    That was a lot more painful than I would have thought.

    https://app.smartsheet.com/b/publish?EQBCT=bc2e2e96b8d24e0db4ad50993a744256

    I would avoid this solution if you plan on having a several thousand row sheet as it will slow down in a hurry with all the text parsing.

    basically what I did was pull the entire column from one sheet into a join function in another sheet, and used the delimiter with an ~ to separate the values. I then used text parsing to sort the values and post them into the appropriate cells in the column in the new sheet.

    If you want you can play with the count function and post without the ~ in the original row, I was about done with this so i just made it a helper column and posted the pretty version in the column beside it.

  • Jim Hook
    Jim Hook ✭✭✭✭✭✭

    I may be wrong but I don't see why simple linking from the first sheet to the second sheet wouldn't work for you. The second sheet can have as many as 5000 incoming cell links if that's enough for you. The only problem with using links is that if you want to get rid of a completed project you can't simply delete the rows on the first sheet without destroying the links to the second sheet. Also, keeping the second sheet projects in the same order if you move some rows up or down in the first sheet (like when you start a new project) won't automatically reorder the projects on the second sheet. That might need to be done using drag and drop rows on both sheets. I do things like that a lot and just reorder sheets as needed.