Project Intake Sheet and Project Metadata Error

We are trying to get two added columns from the project intake sheet to connect to the project metadata sheet. Columns names are Business Opportunity and Project Score. We have added the headers and proper formulas to row 1 of each sheet. However, the new columns are showing an "#Invalid Value" in each.

I have provided screen shots and formulas for each below.

Project Metadata sheet:

Project Intake Sheet:

Any help on the error would be greatly appreciated.

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭

    Hi @JeffV


    Your index is the column from which you need the data. So, your formula should be =INDEX({Business Opportunity column reference}, MATCH([Project ID]@row, {Project ID column reference}, 0) for Business opportunity. Replace the business opportunity column reference in the formula with the project score column reference for the project score column's formula.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • Appreciate quick response and updated formula but still getting same error:

    Note that in the other columns, for instance "Target Start Date" this formula is connecting the data properly:

    =INDEX({Portfolio Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Target Start Date]$1, {Portfolio Summary Header Row}, 0))

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 01/15/24

    Hi @JeffV

    If looks like you may have copy/pasted the formula from the response above versus creating a new {reference} looking at the Business Opportunity column, is that correct?

    The INDEX(MATCH formula suggested by Aravind uses individual column references to find the row/cell to bring back.

    However the formula you have written here - INDEX(MATCH(MATCH - uses an entire sheet reference to look across the whole other sheet, then find the column and the row based on the top-row title.

    If you've added in a new column, double check that your reference: {Portfolio Summary Data} is looking at that new column as well! You may need to click edit reference and re-select the range across the sheet to include new columns.

    Here's more information: Create cross sheet references to work with data in another sheet

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!