Smartsheet form not populating entries in correct row despite deleting rows

sawuzie

Hello! The SS form entry has unfortunately been driving me insane. New entries are supposed to populate at the bottom of the sheet, but I'm getting almost a hundred rows in between the last and second to last entry.

I've read the past discussions and I know SS entries populate in the last unused row, which is why I make sure to delete every row under the last entry. However, my form has inbound links so that some columns populate themselves based on information in another sheet. Once I add this cell link SS adds almost 250 rows on its own and the next entry comes in 100 rows down.

Does this mean I cant have sheets with in bound links and one the populates entries correctly at the same time?

Paul Newcome

@sawuzie If you have something that can be used (such as a student ID) to match on, the formula would look something like this:

=INDEX({Source Sheet Column To Pull Over}, MATCH([Unique ID]@row, {Source Sheet Unique ID Column}, 0))

Paul Newcome

Are you able to provide screenshots for context?

sawuzie

Sure, here's an example sheet:

Here is where I would delete all empty rows below the last non blank row to avoid incorrect row entries from a form.

Now when the Age column is now an inbound cell link, SS automatically adds up to 245 rows. The sheet my cross reference came from only had one row of data, just like this sheet.

Now I've added a row via a form (avoiding the cell linked column), that new entry pops up in row 237. The source sheet had no previously used cells as I deleted all rows to start fresh.

It seems that a cell having an inbound link is now considered a "used" cell, but I want the new entry to populate in the same row as one with an inbound/outbound link

Paul Newcome

There's the problem. You aren't deleting the rows. you are deleting the data from the cells.

Click on the actual row number of the first row, shift+click on the actual row number of the last row, click on the three vertical dots to open the row menu, then click "Delete".

Getting it to populate in a row that already is used (including one that has a cell link in it) requires an update request. Is it possible there could be a formula instead of a cell link used? What exactly are you trying to link in?

sawuzie

Hello,

I actually didn't clear contents from any cells, I only deleted rows altogether because I knew that was only way for entries to populate correctly. The only time empty rows appear below the most previous entry is when an inbound cell link is added. But if I delete all the rows from both sheets, I have other problems.

I tried again and deleted all rows from both sheets, above is my example source sheet and destination sheet, where I want to link "Source Age" to the "Age" column.

Now it did populate correctly from the form entry but the Age column didn't maintain that cell link because the Source sheet only had one row. Ideally, the Source column will be a column that is increasing with more entries.

The sheet I am using this for has both outbound and inbound links, the outbound links take specific info into another sheet for calculations and that other sheet will return back a certain answer into the original sheet. I considered using a formula for cross references, but I cant see how that wouldn't be negatively affected by the constant deleting of rows

Thank you

Andrée Starå

Hi @sawuzie

I hope you're well and safe!

To add to Paul's excellent advice/answer.

• If it's a cross-sheet formula you're using, you would either need to convert it to a column formula or at least have two rows with the formula before, and it would auto-fill for the following and future submissions.

Make sense?

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Andrée Starå The poster has mentioned a few times that it is a cell link that they want pulled down. That is why I asked what exactly is being linked and if a formula could be used in its place.

@sawuzie How are you determining which cell to link to on the other sheet? If there is some logic or it can be matched to a cell (or combination of cells) on the form sheet, we can get a formula in there so that it pulls into every row as forms are submitted.

sawuzie

Hello @Paul Newcome,

The source sheet already filters all the data I need into a column, I just need to cell link that column to populate exactly as it is seen in the destination sheet. The column in the source sheet will increase in rows as more entries are added, so I would expect for it to update the same in the destination sheet.

Is there a column formula that can achieve the same results as a cell link?

Andrée Starå

https://community.smartsheet.com/discussion/comment/390816#Comment_390816

I'd recommend cross-sheet formulas combined within INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

To connect them row by row, you could use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you need in the Destination sheet.

Another option to connect the sheets with new rows could be to use an INDEX formula to collect the row number or a specific id and then use that for the INDEX/MATCH structure if you need to collect more columns.

Make sense?

Would that work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@sawuzie I would suggest an INDEX/MATCH or INDEX/COLLECT with cross sheet references if there is a way to be able to identify which data point needs to be pulled over to the row after the form is submitted.

Do you have a unique identifier on each row that can be used to match on (such as a student ID), or would we be able to reference a series of cells to get a match (such as name, school, bday combo)?

sawuzie

Hello @Andrée Starå thank you for your response.

I don't have a lot of experience with index functions, would you mind providing me an example of what you are explaining with the helper column?

sawuzie

Hello @Paul Newcome

if l’m understanding your question correctly, each row has a sequential entry ID that is unique to that row that can be used a unique identifier

Or under the column “Med ID or Bulk Supply?”each eligible row should either have the selection “Bulk Supply” and the “Impacted Bulk Supply Quantity” column would be nonblank, or “Med ID” and the “Impacted MED IDs” column is non blank.

Could either of these criteria work?

Paul Newcome

@sawuzie If you have something that can be used (such as a student ID) to match on, the formula would look something like this:

=INDEX({Source Sheet Column To Pull Over}, MATCH([Unique ID]@row, {Source Sheet Unique ID Column}, 0))

sawuzie

I created a helper column in my source and destination sheet instead, and I wanted to avoid the #NO MATCH error, so I tweaked it a little to this:

=IFERROR(INDEX({Source Column}, MATCH([Helper]@row, {Source Helper Column}, 0)), " ")

so thank you for the formula!

Paul Newcome

@sawuzie Yes. I always add the IFERROR, but I try not to add it until I know for sure the base formula is working as expected including when trying to help work through a solution on here.

Glad you were able to get it working and happy to help. 👍️

Andrée Starå

https://community.smartsheet.com/discussion/comment/390866#Comment_390866

Happy to help!

I saw that Paul had answered already!

Let me know if I can help with anything else!

Best,

Andrée

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!