Help with Auto Number column formula

SDevine

Hello, I've pretty new with Smartsheets and most of the time I try to use the examples from other posts that I have seen others put up in order to solve most of the problems that I have. Unfortunately, I cant seem to get this problem to work. I have 3 sheets, Source, Helper, and Destination sheets for an Onboarding project that we have. The Source sheet has sensitive information that is at a global level, but we are trying to find a way to update the destination sheet with certain individuals in a specific area, I have created an auto number to individualize the data from the Destination sheet. I have an automation that copies rows from the Source to the Helper Sheet (which only the global individual will have access to), and I'm trying to create either a VLookup, or Index formula to only grab the auto number from the Helper sheet and put it in the Destination sheet. I am then going to use DataMesh to update only certain columns based on the auto number, but I keep coming up with either a circular reference error or Unparseable error while using the formulas. I was able to get the first row using =INDEX({2023 Onboarding Helper Row ID}, 2), but it just copies the same number when it is converted into a function column, or when it is pulled down.

This is the information from the Helper sheet:

This is the Destination sheet that I'm trying to either VLOOKUP or INDEX to populate data from the Helper sheet

I might be wrong, but I think it is possible that I can't do what I'm thinking about doing without having something to reference for either the equations or for DataMesh. Which is really throwing a wrench into things. I was able to the the first one working with this equation:

=INDEX({2023 Onboarding Helper Row ID}, 2)

The equation below is Unpareseable:

=INDEX({2023 Onboarding Helper Row ID}, [Row ID: Row ID])

This is the Incorrect Argument:

=VLOOKUP("CL", {2023 Onboarding Helper Row ID}, 2)

This is the Circular Reference, I read something from someone that stated the auto number isn't seen as a value and needs to be converted into one first which I tried to do:

=IFERROR(VLOOKUP(VALUE([Row ID]@row), {2023 Onboarding Helper Row ID}), "")

We are tracking the new hires for up to 90 days to make sure they all receive the same information and complete the same tasks. We want the rows for the individuals who have completed the process to then go to an archive sheet (which a trigger will be created later to do), I thought this might be important incase there is a reference by row, those rows will probably be changing. I know this is probably long winded, and I might have missed other stuff. But I would really appreciate any help with this.

Ella

@SDevine I understand what you are trying to achieve, but do not believe it is possible. For example if you have the Row ID in the target sheet, you can Vlookup and pull the name from the source sheet, but I just can't see how you would be able to pull the Row ID. There is no criteria that you can put in the formula for Smartsheet to know which Row ID it needs to pull for each row. For such situations we are manually entering the Row ID and then datameshing the rest.

SDevine

Thank you for responding @Ella. I have talked with the team I am working with about this and have essentially told them the same thing. It is unfortunate, but I think I can make this as easy for them as possible.