Error pulling in % Complete in metadata for the current project phase.

Kathy PPT

Hi Community,

Hope your week is starting out well.

I would appreciate any help you can provide.

I am getting Unparseable error in the "Phase % Complete" cell in the Metadata sheet. I'm trying to pull in the "% Complete" from the Project Plan for the "Current Phase", which is "Onboarding".

As you can see, I have the "Current Phase" showing correctly in the project plan. This populates the metadata (via cell link). My formula to pull in the % Complete (96%) in the Current Phase row is causing an issue.

PROJECT PLAN (PP)

METADATA Sheet

Cross Sheet References are:

Range 5 = the % Complete column in the PP.

Range 4 = Current Phase cell in row 2 of the PP, "Onboarding"

Range 3 = The Task Name column in the PP.

I've tried variations of formatting the formula but can't seem to get any further. I also have read the documentation thoroughly, enough to feel like I'm losing my eyesight! 😉

Thank you!

KPH

Hi @Kathy PPT

The unparseable error is caused by the @row in your formula and the missing row index. That's the easy thing.

Now to figure out what to do instead. I see you have the Current Phase in the meta sheet. This formula would return the value in range 5 where the value in range 3 matches that in the Current Phase column of the meta sheet on the same row.

=INDEX(COLLECT({range 5}, {range 3}, [Current Phase]@row), 1)

Does that help?

Kathy PPT

@KPH,

Good morning and thank you for your reply. Your logic made sense; however, I'm now getting Incorrect Argument.

Incorrect Argument: =INDEX(COLLECT({customer name Project Plan Range 5}, {customer name Project Plan Range 3}, [Current Phase]@row, 1))

I need the % Complete value from the row where the current phase actually is in the project plan. In this case it's 96% (circled in red) in the collapsed Onboarding row (see original screen shot).

KPH

Hi @Kathy PPT

Can you move the 2nd from last parenthesis? This needs to close the COLLECT function before the row index part of the INDEX function.

=INDEX(COLLECT({customer name Project Plan Range 5}, {customer name Project Plan Range 3}, [Current Phase]@row), 1)

Kathy PPT

Hi @KPH

I feel like we are so close, but I can't see what's wrong.

#Invalid Ref: =INDEX(COLLECT({customer name Project Plan Range 5}, {customer name Project Plan Range 3}, [Current Phase]@row), 1)

#Unparseable (when I put a space before @row): =INDEX(COLLECT({Clark Builders Project Plan Range 5}, {Clark Builders Project Plan Range 3}, [Current Phase] @row), 1)

KPH

#Invalid Ref means one of the references to another sheet (the bit in in curly braces) doesn’t exist. Can you check them both? Maybe the customer name part wasn't pasted back in correctly?

There is a help sheet on cross sheet references here: https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

You should not have a space between the column name and @row. Use  [Current Phase]@row

Kathy PPT

@KPH

Thanks, I'll keep looking at it.

Appreciate your help!!

Kathy PPT