Replicating Formula

Options

I am having an issue where I have a formula working on one sheet but when I copy the formula to perform the exact same function on a different sheet, it does not work. I'm hoping someone could look at both sheets and let me know what I'm doing wrong.

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey Chris

    Here's a couple of trouble shooting steps to try.

    1) Make sure that the [Office service Contact] column on your new sheet is a Contact field (of the same type - if you allow multi-contacts on one sheet it needs to be on the other as well)

    2) In the formula giving you the Invalid reference error, one by one, delete the cross-sheet reference in your formula, then click the Reference another Sheet in the formula box, go to the sheet and re-insert the reference. Do this for each cross sheet reference in your formula.

    Do either of these steps clear up your error?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 12/29/21
    Options

    Hey @Chris Hockey

    Are you getting an error, or an unexpected result? Is it a cross sheet reference and perhaps the reference links are not inserted yet into into the new sheet? Also, please check the referenced columns in your sheet to make sure they are formatted the same as the original sheet (eg., date columns)

    Kelly

  • Chris Hockey
    Options

    I get an invalid reference error. I don't understand why though since I am referencing the same sheet. I have three sheets. I have an "OfficeServicesContacts" sheet that contains an office and a linked email which is connected to a form. When the user enters a specific office, it populates the contact for that office.


    The formula being used is: =INDEX({OfficeServicesContacts Range - Email}, MATCH(Office@row, {OfficeServicesContacts Range - Office}, 0))

    I want to use this same concept on a new form using the same "Office Contacts" sheet but get an error:


    But the formula is the same:

    =INDEX({OfficeServicesContacts Range - Email}, MATCH(Office@row, {OfficeServicesContacts Range - Office}, 0))

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey Chris

    Here's a couple of trouble shooting steps to try.

    1) Make sure that the [Office service Contact] column on your new sheet is a Contact field (of the same type - if you allow multi-contacts on one sheet it needs to be on the other as well)

    2) In the formula giving you the Invalid reference error, one by one, delete the cross-sheet reference in your formula, then click the Reference another Sheet in the formula box, go to the sheet and re-insert the reference. Do this for each cross sheet reference in your formula.

    Do either of these steps clear up your error?

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!