Cell reference expression or parent reference?

Greetings!

=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

Find more posts tagged with

Comments

Jim Hook

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

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

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

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

Preston,

Thank you for the clarification.

I'm glad you figured it out.

Craig

Wanda T

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