Copying a Sheet & Using Formulas Changed the Form Attached to Original Sheet

Hi SmartSheet Community,

I came across an issue with copying a sheet and the form that was attached to the original sheet was modified from using a formula in the copied sheet.

Original Sheet Name: IHA

Copied Sheet Name: Copy of IHA

INDEX/MATCH Sheet Name: Job Model

After I copied the IHA sheet (File --> Save as New), I used an INDEX/MATCH formula in the Copy of IHA sheet with the Job Model sheet. Somehow, the fields (Job Position & Grade Level) I used the INDEX/MATCH formula in the Copy of IHA sheet had hidden those fields (Job Position & Grade Level) in the form from the original sheet. I compared the two forms - IHA and Copy of IHA -and the URLs were different, so that was not the issue. I had to go into the form and just click "Hidden" to unhide the field because it was automatically selected to hide the field.

Has anyone else had this problem? I would like to prevent this from happening again, but I am not sure if it is out of my span of control or not.

Thank you!

Find more posts tagged with

Forms

Sheets

Formulas

Accepted answers

Genevieve P.

Hi @DesireeJones

If I'm understanding you correctly, the issue is that in your Copied sheet certain Form fields automatically appeared as Hidden in the form, instead of Showing as in the original sheet.

If so, I haven't experienced this before and I'm unable to replicate this. If the form has hidden fields on the source sheet, the duplicate will be hidden as well. If the fields are showing, the duplicate should be showing those fields, too. This article goes through the behaviours with Save as New: Copy a sheet with Save as New

Is it possible that there are more than one Forms created, where one form has hidden fields, and one has showing?

If this happens again, please reach out to Smartsheet Support with a screen recording showing the original sheet and form before it's copied, then the unexpected behaviour in the new, copied versions.

Cheers,

Genevieve

All comments

Genevieve P.

Hi @DesireeJones

If I'm understanding you correctly, the issue is that in your Copied sheet certain Form fields automatically appeared as Hidden in the form, instead of Showing as in the original sheet.

Is it possible that there are more than one Forms created, where one form has hidden fields, and one has showing?

Cheers,

Genevieve

DesireeJones

Hello @Genevieve P.

Thank you for your assistance!

However, the issue is that when I applied formulas into the copied sheet, the fields where I applied the formulas were hidden in the original sheet's form. I didn't apply the formulas to the original sheet. There is only 1 form per sheet with different URLs. When I make changes to the copied sheet, the original sheet's form shouldn't change.

If this happens again I will reach out to SmartSheet Support! Thanks again!

Genevieve P.

Hi @DesireeJones

Would you be able to post screen captures of the issue, but block out sensitive data?

After the second sheet is created, adjustments to this other sheet should not impact the structure of the original sheet at all.

What formulas are you adding to the new sheet?

DesireeJones

Hi @Genevieve P.

The first picture is the original sheet and these were the fields that I was working with. The second picture is the original sheet's form on the backend/editing side. You can see that the Job Code field is hidden, but it is not supposed to be hidden.

To be clear, we have not launched the copied sheet's form just yet, so there are no real submissions. The only way we found out that the fields were hidden in the original form was because the hiring managers were trying to submit the forms but noticed the fields were missing on the open form.

So, I made a copy of the original sheet to practice some formulas for a global integration. Below is what I changed to the copy sheet.

In this case, the IE Specialist II was selected from a dropdown menu as the job posting title. Once the form is submitted, the Job Code and the Reference Zone or Grade Level field is supposed to autofill using an INDEX/MATCH formula in reference to another sheet that has the information.

Below are the formulas used in the Job Code and Reference Zone or Grade Level fields, respectively.

=INDEX({Job Code Number}, MATCH([Job Posting Title]@row, {Job Title Sheet}, 0)

=INDEX({Grade Level}, MATCH([Job Posting Title]@row, {Job Title Sheet}, 0))

The formulas will match the Job Title from the Job Title Sheet and return the Job Code and the Reference Zone or Grade Level fields.

So once these formulas were inputted into the fields on the copied sheet, they were automatically hidden on the original sheet's form (second picture) causing the hiring managers alerting us that there was missing fields.

I hope this helps! Thanks in advance!

Genevieve P.

Hi @DesireeJones

My apologies for the delay! Thank you for this additional information.

It sounds like your set-up in the Copied sheet works well, those INDEX(MATCH formulas should be bringing in the correct data.

However the formulas input into the copied sheet would have no impact on the original sheet. There is no connection between the copied column and the original source column in the first sheet; after the copy has been created these two columns are completely separate, each with their own unique ID. You could even delete out the "Job Code" column in the copied sheet and this would have no affect on the original sheet.

In order for a field to be hidden on the original sheet's form, there would have needed to be an update on that specific sheet. Is it possible that someone else made updates to the source sheet at the same time as you were making updates to the copied version?

If you have access to it, you could check the Activity Log in that original source sheet to see what changes were made on the day that the fields became read-only. (See: Track the history of changes with Activity Log)

Cheers,

Genevieve

DesireeJones

Hello @Genevieve P.

Checking the activity log was my first instinct when this occurred. There were no other changes made except for me copying the sheet and someone else sharing the sheet around the same time. I was so confused because there is no explanation why the fields were hidden if I was working on a separate sheet.

Hopefully it doesn't happen again!

Thanks for your help! :)

Genevieve P.

I completely understand, I would be confused as well! At this point without seeing your sheets all I can do is confirm that the action of Copying a sheet with Save as New has no impact on the sheet that you're copying from. Adding formulas to the new sheet will also not affect the original.

This indicates that something else happened in the source sheet, but at this point I'm not able to determine what that action was. If it happens again, please do let me know!

Cheers,

Genevieve

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.