How to prevent row data from shifting up a row?

We have a data sheet set up within SmartSheet (Sheet A) that feeds off our master sheet (Sheet B) that contains all of the current positions within our company. This automation is set up so whenever a new position is created in Sheet B, it'll auto-feed the position name into Sheet A into Column 28 of the screenshot.

The issue we're running into is that on Sheet A, we also track if the position was reviewed as well as comments and a review date. However, when a new position is added to Sheet B and it auto-feeds to Sheet A, it'll shift all of the comments and everything up a row and skew all of the data.

Is there a way to prevent this or a better way to set this automation up? I currently use this formula to feed the position name into Sheet A from Sheet B for Column28:

=IFERROR(INDEX({Attributes Range 1}, MATCH([Helper Column]@row, {Attributes Range 2}, 0)), "")

I'd use an automation for copying the row but I only need the Title name and not the rest of the information as Sheet B is quite extensive with its data

Find more posts tagged with

functionality

Automations

Conditional Formatting

Accepted answers

All comments

jmyzk_cloudsmart_jp

HI @Ashley Klaers

Does the master sheet (Sheet B) have an auto-number column, preferably in 01 or 001 format?

If so, one way to get the column information is to pre-populate correcting numbers to Sheet A (001, 002, ...99, ... 999, for example) and use INDEX(MATCH to fetch the data from B. This approach does not use the copy rows automation.

I do not understand the " it'll shift all of the comments and everything up a row and skew all of the data." Does this mean you use Modified Date to get a review date?

If so, one way to avoid this is to copy the rows to another sheet (Sheet C) and get the Created Date of Sheet C using some keys like auto-number.

Ashley Klaers

@jmyzk_cloudsmart_jp

It does have a helper column in a stander format (1, 2, 3...).

If I use an INDEX(MATCH) formula to pull data from Sheet B over to Sheet A, will it update itself if a row is removed from Sheet A?

I'll admit the INDEX(MATCH) combo is one I haven't had to use yet so they're pretty new to me!

jmyzk_cloudsmart_jp

Hi @Ashley Klaers

As your INDEX(MATCH formula has an error handling with IFERROR(INDEX(MATCH()),""), it will update itself, making the cell empty with (""). If you want a warning, you can change the formula to something like this.

IFERROR(INDEX(MATCH()), "position does not exist at the master")

Copy only certain columns

I have a similar dilemma of coping rows with an extensive number of columns, as your situation in the following comment.

I'd use an automation for copying the row but I only need the Title name and not the rest of the information as Sheet B is quite extensive with its data

I figured out a method to copy only certain columns using a helper sheet.

Please take a look at the published demo dashboard below.

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

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.