Unparseable string on Project Metadata Sheet - where is the portfolio summary data coming from?
I'm having an issue when attempting to use a workspace from the PMO office template set that didn't use the portfolio folder (which may not be relevant--it just has all the portfolio folder sheets and reports at root level.)
When I attempt to update the project metadata in the individual project folder, I get a message of "#unparseable" or "#no match). If I select "Edit References" I see
{Portfolio Summary Data}
{Project ID}
{Portfolio Summary Header Row}
My question is: "Where does the Portfolio Summary Data" come from? Is that the intake sheet?
I know my column names must be identical and that the order of columns must be identical, must I also have exactly the same columns in my project metadata sheet as the project intake sheet?
I can't figure out why it's not finding a match, but mostly because I don't know where portfolio summary data lives.
Thanks to anyone who can enlighten me! : - )
Answers
-
If you go the cell that is returning the error, click on the Reference eg {Portfolio Summary Data} within the formula, you should get an Edit Reference option on the tooltip helper. This should open a dialog box which shows you the sheet and cell/column that it is using for the source data.
I am just off into a client meeting, but feel free to reach out if you'd like a zoom so that I can explain the template functionality for you.
Kind regards
Debbie
-
Wow. Thanks for the quick response. That is incredibly generous of you. I can see and edit the references, but it still isn't clear to me where Portfolio Summary Data is coming from (in my references, I've linked it to the project intake sheet, but I don't see Portfolio Summary).
The Project Metadata instructions read, "This sheet acts as a landing place for key project information. Intake data comes from the Portfolio Summary sheet via lookup formula", but it doesn't say where the Portfolio Summary sheet is.
-
Are you able to take a zoom call?
I'm happy to call you quickly and explain how the template fits together.
My email address is debbie.sawyer@smarterbusinessprocesses.com
Email me and I'll send you a zoom link.
-
Wow!
I just downloaded the template folder and saw the #REF errors in the project metadata sheet.
The {Project Summary Data} is referring to the whole of the intake sheet as a named range! I can't see how that works (that isn't to say it doesn't work, I just can't get my head around it).
To return the correct values in the Project Metadata sheet I would recommend doing the following:
In the Project Name column - change
=INDEX({Project Intake Sheet - Project Name}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Project Name]$1, {Portfolio Summary Header Row}, 0))
to
=INDEX({Project Intake Sheet - Project Name}, MATCH($[Project ID]@row, {Project Intake Sheet - Project ID}, 0))
follow this logic on the other #Ref errors on the Project Metadata sheet. I had to create the range that is emboldened here. I am happy to help if required.
Good luck!
Debbie
-
I did run a webinar a couple of years ago on how to download this template and update it to make it work. My tips and tricks sheet on this was 52 lines long! ha ha
If you email me, I can send you a link to the webinar or a share to the workspace that I used in the webinar which has my tips and tricks sheet inside.
debbie.sawyer@smarterbusinessprocesses.com
-
I finally figured out how to fix this error. A few notes for anyone else who might run into these issues when trying to use the template:
1). You don't need the exact columns in your metadata sheet as in the intake (e.g., you don't need to include description), but the order of the columns and the column names must match exactly. This seems easy enough, but I had inherited lots of different metadata sheets with different column names and column orders.
2). The elusive {Portfolio Summary Data}, {Project ID}, {Portfolio Summary Header Row} are indeed managed in "Manage References". They should reference the Project Intake Sheet.
{Portfolio Summary Data}, - you should select the entire sheet as the reference.
{Project ID}, - You should select the entire Project ID column as Reference
{Portfolio Summary Header Row} - You should be sure that you have a header row in text format that exactly matches the column names--I was missing the header row (I had to insert a row at the top, change my column properties to text/number so I could create the header texts for the referenced header row and then change my column properties back on my intake sheet).
You can create your own references in manage references, but be sure in the "Sheet reference name" field at the top that you name them exactly as they are spelled in your formula.
