Need help with adding additional reference sheets to this formula.

Joyce W
Joyce W ✭✭
edited 05/09/23 in Formulas and Functions

Hello!

I have a sheet name "Process Issues" with a series of information in this sheet. I have 3 other sheets named: Deposit, Lending, Payments. I have set up that when the project ID from the "Process Issues" sheet, is entered in the applicable deposit, lending and payments sheet, that the fields are auto populated from the "Process Issues" sheet. This formula I have working.

I have a "Notes' column in each of the deposit, lending, and payments sheets. I want write a formula that would copy over the notes back to the "Process Issues" sheet based on the project ID from the Deposit, Lending and Payments sheet.

I am now stuck on this formula and I am only able to get the formula to work with just one sheet (lending), and I can't get the deposit and payments in the formula. How can I add the other two sheets into the formula? Any help would be appreciated! Thanks!

=INDEX({Lending Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH(Notes$1, {Lending Summary Header}, 0))

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would create separate INDEX/MATCH statements and "add" them together.

    =INDEX/MATCH + CHAR(10) + CHAR(10) + INDEX/MATCH + CHAR(10) + CHAR(10) + INDEX/MATCH


    Note: CHAR(10) is a line break. Using the wrap text feature should help keep things pretty clean.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Joyce W
    Joyce W ✭✭

    @Paul Newcome : Thank you! My function and formula skills are not great. Either that I'm not understanding the format of the statement or I am doing something totally wrong.

    This is what I have:

    =INDEX({Lending Summary Data}, MATCH($[Project ID]@row, {Project ID LD}, 0), MATCH([Target End Date]$1, {Lending Summary Header}, 0)) + INDEX({Deposit Summary Data}, MATCH($[Project ID]@row, {Project ID DP}, 0), MATCH([Target End Date]$1, {Deposit Summary Header}, 0)) + INDEX({Payments Summary Data}, MATCH($[Project ID]@row, {Project ID PM}, 0), MATCH([Target End Date]$1, {Payments Summary Header}, 0))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Is that not working as expected? Are you getting an error message or an unexpected output?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Joyce W
    Joyce W ✭✭

    Hi @Paul Newcome,


    The error message I am getting is #UNPARSABLE.

    This is the formula I have working: =INDEX({Lending Summary Data}, MATCH($[Project ID]@row, {Project ID LD}, 0), MATCH(Notes$1, {Lending Summary Header}, 0))

    So I basically want to add this to the formula above make it one single formula:

    =INDEX({Deposit Summary Data}, MATCH($[Project ID]@row, {Project ID DP}, 0), MATCH(Notes$1, {Deposit Summary Header}, 0))

    Plus

    =INDEX({Payments Summary Data}, MATCH($[Project ID]@row, {Project ID PM}, 0), MATCH(Notes$1, {Payments Summary Header}, 0))

    Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide a screenshot of the formula actually in the sheet similar to the screenshot below?


    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Joyce W
    Joyce W ✭✭
    edited 05/15/23

    @Paul Newcome: Thank you. Hope this screen shot will help!

    This is the working formula:

    =INDEX({Ops Lending Summary Data}, MATCH($[Project ID]@row, {Project ID LD}, 0), MATCH(Notes$1, { Ops Lending Header}, 0))

    So I need the other sheets Deposit and Payments combined to the formula above

    =INDEX({Deposit Summary Data}, MATCH($[Project ID]@row, {Project ID DP}, 0), MATCH(Notes$1, {Deposit Summary Header}, 0))

    Plus

    =INDEX({Payments Summary Data}, MATCH($[Project ID]@row, {Project ID PM}, 0), MATCH(Notes$1, {Payments Summary Header}, 0))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What about the formula you are getting the error on that is adding each of them together?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try removing the + from the end or adding in the third INDEX.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Joyce W
    Joyce W ✭✭

    @Paul Newcome : It's still not working. It's giving an error of either Invalid Operation or No Match.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. In that case, try wrapping each INDEX in an IFERROR.

    =IFERROR(INDEX(….., MATCH(…..), MATCH(…..)), "") + IFERROR(INDEX(….., MATCH(…..), MATCH(…..)), "") + IFERROR(INDEX(….., MATCH(…..), MATCH(…..)), "")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Joyce W
    Joyce W ✭✭

    Hi @Paul Newcome :

    I have tried the IFERROR in front of the index and get an error of "Incorrect Argument Set"

    =IFERROR(INDEX({Ops Lending Summary Data}, MATCH($[Project ID]@row, {Project ID LD}, 0), MATCH(Notes$1, {Ops Lending Header}, 0))) + IFERROR(INDEX({Ops Deposit Summary Data}, MATCH($[Project ID]@row, {Project ID DP}, 0), MATCH(Notes$1, {Ops Deposit Header}, 0))) + IFERROR(INDEX({Ops Payment Summary Data}, MATCH($[Project ID]@row, {Project ID PM}, 0), MATCH(Notes$1, {Ops Payment Header}, 0)))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    It is because you are not finishing out the IFERROR statements. You need to tell each one what to do if there is an error.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Joyce W
    Joyce W ✭✭

    @Paul Newcome : Yes! It worked! Thank you so much for your help!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!