Cross Sheet Reference Shows #REF When Row Is Moved

I have an intake form setup in one sheet and in another sheet I have a bunch of formulas to format the data from the form into a csv that can be used to upload the information into Jira. I have everything working, except when I have the row removed from the intake sheet (because it's been completed) I get #REF in my sheet that does the formatting. I'm using IF(INDEX({Type of Request}, 1) = at the beginning of my formulas so that the formulas are only looking at the first row in the intake sheet, however, I can't use the entire column in the cross sheet reference because that doesn't work. So I currently have the cross sheet references on each field in the first row, which then fails when the row is removed.

Is there a way to reference the first cell in a column no matter what happens with previous data in the cell? Is there a better way to complete this (I do have an Excel sheet setup to essentially do the same process by copying the intake info out of Smartsheet but I'd like to keep as much work in Smartsheet as possible)?

Find more posts tagged with

Formulas

References

Accepted answers

jmyzk_cloudsmart_jp

Hi @Sarah123

I tried to recreate your error by making the {Type of Request} range to reference only the Type of Request in the 1st row. Are you referencing the range in the same way?

If so, referencing all rows of the column solves the issue.

I don't understand what you meant by "I can't use the entire column in the cross sheet reference because that doesn't work," but as far as I know, that works to reference the first row.

In the demo sheet, Format Sheet, Type of Request 1 uses a range that references only the first row.

Type of Request 2 uses a range that refers to the entire column or all column rows.

In the Sheet Reference Manager, I can see the two ranges.

And below is the range that references only the 1st row.

Then, I deleted the 1st row.

As expected, I got the #INVALID REF error at Type of Request 1, but Type of Request 2 keeps referencing the first row of the intake sheet.

When I revisited the Sheet reference Manager, the range that references only the 1st row is gone.

What remains is the range that references all rows.

I input a new request using a form.

https://app.smartsheet.com/b/publish?EQBCT=4e40203c73e24670b5bc37b6f52b66c9

The Type of Request 2 keeps looking at the first row of the intake sheet, now the Bug Report, again.

https://app.smartsheet.com/b/publish?EQBCT=ae600741c992458a8a39354196e62c95

So, I would just use a range that references the entire column in the cross-sheet reference.

All comments

jmyzk_cloudsmart_jp

Hi @Sarah123

I tried to recreate your error by making the {Type of Request} range to reference only the Type of Request in the 1st row. Are you referencing the range in the same way?

If so, referencing all rows of the column solves the issue.

I don't understand what you meant by "I can't use the entire column in the cross sheet reference because that doesn't work," but as far as I know, that works to reference the first row.

In the demo sheet, Format Sheet, Type of Request 1 uses a range that references only the first row.

Type of Request 2 uses a range that refers to the entire column or all column rows.

In the Sheet Reference Manager, I can see the two ranges.

And below is the range that references only the 1st row.

Then, I deleted the 1st row.

As expected, I got the #INVALID REF error at Type of Request 1, but Type of Request 2 keeps referencing the first row of the intake sheet.

When I revisited the Sheet reference Manager, the range that references only the 1st row is gone.

What remains is the range that references all rows.

I input a new request using a form.

https://app.smartsheet.com/b/publish?EQBCT=4e40203c73e24670b5bc37b6f52b66c9

The Type of Request 2 keeps looking at the first row of the intake sheet, now the Bug Report, again.

https://app.smartsheet.com/b/publish?EQBCT=ae600741c992458a8a39354196e62c95

So, I would just use a range that references the entire column in the cross-sheet reference.

Sarah123

Thank you for the very detailed answer! I was having an issue with using the reference for the whole column for some reason but I decided to try over and it worked this time.

jmyzk_cloudsmart_jp

Happy to help!😁

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.