NETWORKDAY error using cross-sheet reference

Kathy PPT

I would appreciate help figuring out this error please.

I'm trying to determine the number of working days from the start of the project to when it went live (Hypercare). The calculation is in the Metadata and references the Enable Service date field in the project plan. It returns #UNPARSABLE error. # days to Hypercare is a text/numeric field

Since it's the Metadata, there is only 1 row so it's tied directly the Enable Services date field in the project plan. When I view the reference, it's going to the correct field.

Thanks in advance!

Kathy PPT

Thanks @Kelly Moore, that worked. I didn't think I needed it since there is only 1 row in the metadata sheet.

Kelly Moore

Hey @Kathy PPT

To answer the @row question above, you always have to designate a row position for smartsheet to find the data. All sheets work using the coordinates of rows and columns, regardless of how many rows are in use.

The syntax for your formula throwing the error (above) is not what smartsheet expects for NETWORKDAY. If you're not already using it, I encourage you to keep an eye on the formula helper or wizard - I don't know what's it called, that shows you what smartsheet is expecting AND the highlighted yellow portion shows you where smartsheet thinks you are in the formula. I use this frequently on complex formulas when I'm double-checking parentheses and commas (my mortal enemies in formulas 😉)

If I understand correctly, you only want the formula to run when the % complete is greater than zero. An IF statement should have worked for this. To rule out empty cells perhaps generating a false result, we'll account for that

=IF(AND(ISNUMBER([% Complete]@row),[% Complete]@row>0),NETWORKDAY([Start Date]@row, {Darigold Project Plan Range 1}))

Will this work for you?

Kelly

Kelly Moore

Hey @Kathy PPT

You are missing the @row as part of the [Start Date]

it should read [Start Date]@row

Kelly

Kathy PPT

Thanks @Kelly Moore, that worked. I didn't think I needed it since there is only 1 row in the metadata sheet.

Kathy PPT

@Kelly Moore , hoping you could answer another question.

Using that same formula, how do I make it conditional so it only shows if the "% Complete field" on the project plan sheet is greater than 0? First pic is the one that works.

This attempt is to use the condition. It gives #INVALID REFERENCE. Using =IF didn't work either.

Kelly Moore

Hey @Kathy PPT

To answer the @row question above, you always have to designate a row position for smartsheet to find the data. All sheets work using the coordinates of rows and columns, regardless of how many rows are in use.

The syntax for your formula throwing the error (above) is not what smartsheet expects for NETWORKDAY. If you're not already using it, I encourage you to keep an eye on the formula helper or wizard - I don't know what's it called, that shows you what smartsheet is expecting AND the highlighted yellow portion shows you where smartsheet thinks you are in the formula. I use this frequently on complex formulas when I'm double-checking parentheses and commas (my mortal enemies in formulas 😉)

If I understand correctly, you only want the formula to run when the % complete is greater than zero. An IF statement should have worked for this. To rule out empty cells perhaps generating a false result, we'll account for that

=IF(AND(ISNUMBER([% Complete]@row),[% Complete]@row>0),NETWORKDAY([Start Date]@row, {Darigold Project Plan Range 1}))

Will this work for you?

Kelly

Kathy PPT

Thanks Kelly, that works!