New form entry data autofills then is quickly overwritten with incorrect formula information

Hi All

This error appears to have started only recently (within last 2-3 weeks).

We have a sheet which takes form submission data and posts to the first line for each new entry. A step in the process is there is a formula which joins several cells together as a key cell.

=JOIN([Art Reference]1:Description1, " - ")

This system has been in operation for at least 3 years and has been auto-filling without any issues. Now we're noticing when the new line entry goes in, it has the same formula reference as the line below - i.e. Line 2 (which was previously Line 1) has had its row reference change to "2", however Line 1 (the new entry) is also referencing the Line 2 cell references for the formula.

We did some testing by submitting dummy entries on a separate tab and then switching and refreshing the sheet to see the new line. We witness the correct formula with the Line 1 cell references immediately come in but almost instantly suggesting

=JOIN([Art Reference]1:Description1, " - ")

is the formula being auto-filled. but almost instanly that is then overwritten in line 1 with the same formula from Line 2:

=JOIN([Art Reference]2:Description2, " - ")

If we manually copy & paste the cell from any other line back over Line 1 it fixes itself same as Excel does when you have a formula without absolute addressing set. It is as if there something going in an copying Line 2's cell contents and pasting it into Line 1.

If anyone could shed some light on why thsystem is doing this it would be greatly appreciated.

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi @15UZU

Instead of using a specific row reference, I would suggest using @row. This will make sure your formula is always looking in this current row for the data:

=JOIN([Art Reference]@row:Description@row, " - ")

See: Create Efficient Formulas with @cell and @row

This would also allow you to apply the formula as a Column Formula! See: Set Formulas for All Rows with Column Formulas

Cheers,

Genevieve

GCFrog1

We have a problem that sounds very similar to that first reported - possibly the same issue? We similarly have a sheet that we've been using for a couple of years without issue, but had problems for the last 2-3 weeks. This sheet is populated using inputs from a form, with the form inserting the result in a new row at the top of the sheet.

The form allows me to input some variables (e.g numbers of different types of hardware), these are then inserted into the relevant columns, and then in some other columns are some formulae containing absolute references (i.e. with "$") referring to some fixed cells below the 600-odd previous form submissions with operators based on the variables (e.g. the number of a product might be a variable in the field, and below the entries might be the unit price so multiplying these two gives the total sale value). The primary column contains a unique project name which is one of the fields in the form (first field in fact). This approach is a bit clumsy for sure, I inherited the sheet some 12 months or more ago but don't have the resource to substantially change it at this time.

Here's where is gets interesting - what's started happening in the last 2-3 weeks is:

We make a form submission, this correctly inserts a row at the top of the sheet, saves the variables entered in the form, and then some formulae with the absolute cell references automatically populate a number of adjacent column completing the sequence in that column. This works perfectly first time - the absolute reference remains intact, the formulae in this newly inserted row all refer to the same cells at the bottom of the sheet for each reference For the purposes of this example, let's call this newly inserted project "test1", and at this point the resulting calculations for "test1" then are exactly as expected
Then, we use the same form to enter the same variables (for ease of testing, different variables have same effect), save for a different project name (let's call it "test2"). But here's where we run into a problem - the form inserts a new row at the top of the sheet and correctly saves the variables entered in the form. So far so good. But in all of those columns where there are formulae using combining these variables with cells referred to via absolute cell references to at the bottom of the sheet, the absolute cell references that were previously correct in the earlier "test1" entry, are now all offset by one row, subsequently "test1" results are now incorrect. Subsequently the newly entered "test2" does not return a valid response, as in the affected columns (all columns with absolute references to cells at the bottom of the sheet), there is no longer a clear sequence to follow. This can be reproduced with 100% reliability, and while I've tried logging out of Smartsheet and opening the form again between "test1" and "test2", there is no difference. If I manually drag the formulae up into these newly inserted two rows from row 3 or further down, they come right, but our users can't do this, and then if the form is used for "test3" that will come out right, until "test4" at which point "test3" references are once again offset, which in turn results in lots of blank cells in "test4" where the sequency can't be identified

In short then, while the form only allows a new entry at the top, a second form submission "corrupts" the previous entry as it is pushed down one row without fail. Any ideas? Thank you

Genevieve P.

Hi @GCFrog1

Thank you for this detailed explanation! This sounds like unexpected behaviour (as does the original post on this thread) so I have forwarded this Community post to our Support team for context as they look into form submissions and formulas.

In the meantime, I would suggest having your Form Submissions go to the bottom of the sheet instead of to the top.

Cheers,

Genevieve

Ross C

Hi Genevieve,

We are seeing the same problems. I have ~10 worksheets that pull similar data (but each pulls from a different form) and they all started seeing the above mentioned errors at the same time.

Ross

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.