Replicating Formula

Chris Hockey

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.

Kelly Moore

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

Kelly Moore

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

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

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