Replicating Formula
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
-
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
-
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
-
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))
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!