Dynamic vlookup for milestonetracking across multiple projects

Thimmo Borger

Dear community,

I currently need to track budgets for several projects which are linked to certain milestones. To simplify it a lot , there are two Smartsheets.

1st Smartsheet - Contains all projects with a monthly budget. E.g. Project X needs to complete the Pilot phase with X amount of budget until the end of April.

2nd Smartsheet - Due to the sheer amount of projects we separated the milestone tracking for the projects.

Currently the projects come and rarely go (project cancelled) on a weekly basis. They also change in e.g. names or contract affiliation.

My Issue: I need a formular which takes place in the 1st Smartsheet in the last column. It needs to take a look at the projectname (PARENT) and the milestone (CHILDREN) -> find the information in the 2nd Smartsheet and only gives me the percentage of the belonging Milestone. Here: 20%

I could also link the cells directly with the percentages in the 2nd Smartsheet while I import the premisses, but thats such a lot of work which I would like to be automated.

My current forumlar looks like this:

=INDEX(CHILDREN(MATCH([Project]3;[Project with milestones]:[Project with milestones];0)); MATCH([Planned milestone for month X]3;CHILDREN([Milestone completion (%)]:[Milestone completion (%)]); 0))

The Italic written words are linked columns from the 2nd Smartsheet.

I orientated myself by looking into this community post: https://community.smartsheet.com/discussion/38676/using-children-to-define-range-in-vlookup

Because of the fact that the milestones always have the same name and certain projects dont have all or other milestones, the formular needs to find the correct project at first. Thats the reason why I can't use the formular from the posted link.

Do you have any idea how to amend the formular or is it impossible (smartsheewise) to establish such formular?

Thank you very much in advance!

DeinemNachbarn

Eric M Oliveira

Hello,

Currently, we don’t have a method to utilize cross sheet hierarchy formulas in Smartsheet but this will be considered as a possibility for future development. This is further outlined here: https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas

The following functions don’t support references from another sheet: CHILDREN, PARENT, ANCESTORS. Using a reference from another sheet with these functions will result in an #UNSUPPORTED CROSS-SHEET FORMULA error in the cell containing the formula. For more information on this and other formula errors, see Formula Error Messages.

You can pull the child value desired utilizing an INDEX(MATCH()) similar to that which you have created. INDEX(MATCH()) functions currently only allow for one criterion to be utilized as the search value. You may want to have a column on your referenced sheet that contains some form of unique identifier per row which indicates the specific desired row you're looking for.

You can also utilize a JOIN(COLLECT()) formula instead if you wish to search and pull a specific value on another sheet based on multiple criteria. To utilize the Parent row name and the Child value as criteria you'll want to create a helper column that contains the Parent name for the Child row at the Child rows level. JOIN(COLLECT()): https://help.smartsheet.com/function/collect

For example, Milestone 3 is apart of Project 1, Milestone 3 is in row 6, you could create another column with the formula =PARENT([Project with milstones]@row) and place the formula in that column at row 6, it will reference Milestone 3 and pull its parent value, Project 1. You would then have the JOIN(COLLECT()) formula on the recipient sheet pull the value in the "Milestone completion (%)" column based on the parent value and Milestone 3. The formula could look similar to this:

=JOIN(COLLECT({Milestone completion (%) Range}, {Parent Value Range}, [Parent Name]@row, {Project with milestones Range}, [Milestone Name]@row) ", ")

Note: If you continue to experience undesired results with the formula I would recommend connecting with our Technical Support team that way we can jump on a screen share session to help achieve your desired result. https://help.smartsheet.com/contact

Have a wonderful day

Cheers,

Eric

Smartsheet Technical Support.