Auto Number + INDEX/MATCH Numbers Misaligning

Hey,

I've created a sheet that feeds into another sheet using a helper column and using INDEX/MATCH

=INDEX({SourceMonth}, MATCH([Helper Column]@row, {SourceNumber}, 1))

(Example of pulling the month from the other sheet onto the primary sheet using the Helper Column)

However, sometimes, the helper column is randomly going in a different order (...120, 121, 145, 124, 127, 132... example of the sequence that was messed up), messing up the entire sheet and rows will no longer be pulled properly. The fix I have to do is delete the column and reassign the column as a resource and fix all the formulas again.

Is there a better way of fixing this so I

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Are you able to provide some screenshots for context?

BELHamaida

I don't have any screenshots at the moment since I had to fix it ASAP to get the sheet up and running during business hours.

The numbers seem to be fine now but once a line is deleted, or something is moved around, it messes up all of the numbers to follow. Is there a more efficient way of doing this?

Paul Newcome

Can you still provide screenshots? I can't visualize how things are structured just based on your description to even begin to understand where something might be going wrong.

BELHamaida

This is the raw data sheet, the first column is the helper column in which counts up per row. All of these values are placed on the sheet I will show below:

163 does not show up and things are not properly syncing up. Now if I delete row 163, it will continue to 164 from 162 which results in me having to completely delete the helper column and remake it.

Paul Newcome

Ok. Is there a reason you are using this to pull the same data over on every row to a second sheet instead of using a report?

BELHamaida

We use the sheet with the copied rows to expand on data that has already been entered in a previous step in our sales process. This just relays that information.

The information has to be visible to the user in the second sheet in order to complete the data entry. A report as far as I know doesn't allow for input of data.

Paul Newcome

A report does allow for input of data as long as the user inputting the data has the proper access to both the report and the underlying sheet(s). Do you happen to have access to Dynamic View?

BELHamaida

https://community.smartsheet.com/discussion/comment/413305#Comment_413305

I do! Is it possible to set it up exactly how I have it (sheet pulling information from another sheet, and empty columns for each row for additional information inputting)? I fear this sheet is going to break and we will lose valuable data all because it's not pulling correctly so if this feature is a lot better, I'm open minded.

BELHamaida

On second thought, after experimenting it, it seems like I don't have it. Even though it'd be super nice to have... it just says evaluation on the top right. I assume there's an additional cost even with a business license?

Paul Newcome

Yes. There is an additional cost with a Business License.

Since the report does allow for the inputting of data, will that work?

What you are trying to accomplish is technically possible with formulas, but it is rarely the most efficient way except under very specific circumstances. I would rather explore other methods that are more efficient and easier to set up for their feasibility first before getting into more complex setups that rely on cross sheet references and can drastically limit capabilities and/or flexibility.

BELHamaida

If it allows for inputting of data and completely eliminates the use of using auto-number helper columns (it's been a nightmare for me), I'll give it a shot and report back. Dynamic View was perfect, I hope I can replicate something as easy as that in reports.

Paul Newcome

The biggest potential drawback with a report is that users need to have access to both the report and the underlying sheet(s) to be able to do anything in the report. This can cause security related concerns if people need to be able to view and/or edit specific columns while having absolutely no access at all to the rest of the data.

Dynamic View allows for the viewing and editing of data without requiring the access to the underlying sheet(s).

BELHamaida

Can they have only viewing access and still see the reports w/o being able to edit?

Additionally, how is a report able to be edited?

Paul Newcome

If they have view only access to both the sheet and report, they will be ale to view the data in the report. If they need to edit anything, they need to have editor access to both the sheet and the report.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.