Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Cell reference expression or parent reference?

Preston
Preston ✭✭✭✭✭
edited 12/09/19 in Archived 2015 Posts

Greetings! Smile

 

=Task$344 + " - LAYOUT - " + [Assigned To]345

 

I have a formula in a child row here. Task$344 refers to the parent row, and [Assigned To]345 refers to an adjacent cell on the same row. When I copy and paste the parent and child rows, Task reference remains locked to parent row 344 and Assigned To autofills to the new row number. I have to manually change the parent reference each time I paste in new rows.

 

The options , as I understand, are:

1. Lock the reference using $.

2. Autofill the reference which is done automatically.

 

 

My question is: is there a way or formula to reference parent rows whilst still utilizing drag and drop? Such as

 

 

=Task345-1

 

I would like to drag and drop, but I would like the child rows to have their reference locked to the parent.

 

Thank you,

Preston

 

Comments

  • Jim Hook
    Jim Hook ✭✭✭✭✭✭
    edited 12/30/15

    Unless I'm misunderstanding something all you need to do is to take the "$" out of the parent row reference and your problem will be fixed. Then you can drag and drop the parent row (and children rows automatically follow) anywhere and the references will automatically adjust. There is no difference between referencing parent and child rows.

  • Preston
    Preston ✭✭✭✭✭

    Thanks for the reply, Jim! :)

     

    Can I just delete this topic? After tinkering with this a little more, I noticed where the flaw in my thought process was!

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Preston, 

     

    I'm curious what part you missed. When I read your initial comment, I thought of a set of data like this:

     

     

    where the number of children rows are not fixed and one would want to always have a reference to a parent row.

     

    In that case, I can think of ways to do that but having a way to reference the parent row could be a useful shortcut.

     

    What did I miss in your original problem?

     

    Craig

     

    Screenshot_123115_121344_PM.jpg

  • Preston
    Preston ✭✭✭✭✭

    Hi Craig,

     

    My mistake was thinking that I needed to lock the parent reference. In my formula, the actual tasks are in all caps; LAYOUT, MATERIAL, BUILD, INSTALL. 

     

    =Task$344 + " - LAYOUT - " + [Assigned To]345

     

    I originally wanted to be able to drag and drop the formula, but that isn't my method now as I need the tasks in each row. So, I need to copy a parent and it's children with the formatting to accomplish my end goal. I don't have to use ($) to lock in either reference because it "remembers" the offset. The offset is what I thought I needed, but that functionality is already there! Duh! :)

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Preston,

     

    Thank you for the clarification.

    I'm glad you figured it out.

     

    Craig

  • Query - I am trying to create sub-tasks (child) that reference the Parent row and then a task (for ease of reporting)

     

    TASK

    Parent:  Partner A (Row 1, column 1)

    Child 1:  Partner A - Initial Meeting (Row 2, Column 1, etc.

    Child 2:  Partner A - Contract Signed

     

    A formula like works, however not for 'repeatability'

     

    =Task$1 + " - Initial Meeting"

    =Task$1 + " - Contract Signed"

     

    Now I want to copy those three rows, change the name in the parent Row and it will update the 'leading' section of each child

     

    The challenge - because I have to absolute reference row 1 in the first instance, each copied (repeated) set of partners refers to that first partner (PARENT), instead of referring to the new parent row.

     

    Ideal Formula:

    =PARENT + " - Initial Meeting"

    =PARENT + " - Contract Signed"

     

    Is this use of PARENT possible, every attempt I make results in an error message

     

    NOTE:  Work around; create the set of milestones using absolute reference, then before copy/paste, remove the absolute reference ($) in each row.  Not ideal as if you add a new milestone the copy/paste can be problematic - it's a tad inefficient not being able to use the PARENT in the formula

     

This discussion has been closed.