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)?
Best Answer
-
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.
The Type of Request 2 keeps looking at the first row of the intake sheet, now the Bug Report, again.
So, I would just use a range that references the entire column in the cross-sheet reference.
Answers
-
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.
The Type of Request 2 keeps looking at the first row of the intake sheet, now the Bug Report, again.
So, I would just use a range that references the entire column in the cross-sheet reference.
-
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.
-
Happy to help!😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!