Need to pull data from row above for formula without using actual row number with
I need to pull data from the row above without having to redo the lower row cell formulas for row #'s every time a new row is added.
Example: From the image above, If I add a row between rows 3 and 4, I need the new row 4 to capture the Last Show Date Engagement City to read the row 3 value of Sun 08-Aug-2021 AND the new row 6 (formerly row 5) Last Show Origin to read the new row 5 value for the Last Show Date Engagement City.
Best Answer
-
I suggest inserting two additional columns.
An auto number column (called "Auto") with no special formatting needed.
And a text/number column (called "Row") with the following column formula:
=MATCH(Auto@row, Auto:Auto, 0))
Finally the column formula to pull the date from the row above in the other column would be:
=IFERROR(INDEX([Last Show Date Engagement City]:[Last Show Date Engagement City], Row@row - 1), "")
Answers
-
I suggest inserting two additional columns.
An auto number column (called "Auto") with no special formatting needed.
And a text/number column (called "Row") with the following column formula:
=MATCH(Auto@row, Auto:Auto, 0))
Finally the column formula to pull the date from the row above in the other column would be:
=IFERROR(INDEX([Last Show Date Engagement City]:[Last Show Date Engagement City], Row@row - 1), "")
-
Paul,
Thanks!!! Worked like a charm!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!