How to create a Main Sheet with sub-task rows that uses data from another sheet?

Rajesh Awasthi

Hello everyone,

New user here and eager to get going with setting up my Smartsheet.

I would like to create a main sheet that contains the name of person and the name of the location to where they are to be dropped off and picked up from along with when the transportation is to begin. Ideally, the address locations would be sub-task rows.

One person can have up to a maximum of 2 pickup locations and 2 drop-off locations. For example odd weeks pick up at 123 Main Street and drop off at 400 Boulevard Principal. Even weeks pick up at 222 Holiday St and drop off at the same address.

Instead of having extra columns in the main sheet to handle all the possible outcomes, I would want to have a second sheet that only contains the Name of the person and 1 row for odd weeks and another row for even weeks.

Example Main Sheet:

Ron Mack attends Head Office

--- 123 Main St / 400 Boul Principal

--- 222 Holiday

Jill Jacks attends Warehouse

--- 150 Drury Lane

Thank you in advance.

Genevieve P.

Hi @Rajesh Awasthi

Sorry for the delay in responding!

For cross-sheet formulas, the lookup ranges won't be able to identify Child Rows in the same way as formulas in-sheet. Because of this, you'll need some sort of indicator with the child rows to identify what the name of the Parent is, in that second sheet. You could do this by having a "helper" column that holds a Column Formula of:

=PARENT([Primary Column]@row)

Then this will bring the name of the Parent in the Primary Column next to each of the locations. Once you have that on the same row, you can build a formula in your original master sheet to look into this helper sheet and identify which address to pull in.

I'd be happy to help you craft that formula, but it will depend a bit on your sheet set-up and what the process actually needs to be. For example, are you wanting the Location column to update based on Today's Date and what the helper sheet says the address will be?

In that case, you'll need an indicator in the row of your helper sheet to say if it's an Odd or Even week location. Then you can build either an INDEX(MATCH formula to bring across the location, or an INDEX(COLLECT formula if you have multiple criteria. Paul has a great example of an INDEX(COLLECT formula in this post, here.

Let me know if this makes sense! If you need further help, screen captures of both sheets would be ideal, but please block out any sensitive data.

Cheers,

Genevieve