Please help: Index matching formula not populated new lines of data

Nero Naidoo

Hi, I have an index matching formula from multiple source sheets based on a condition in th destination sheet. It seems to work fine if I change the condition on existing data but it does not populate on new lines of data. I have set the formula to be a column formula:

=IFERROR(IF(CONTAINS([Process Area]@row; "Intercompany"); INDEX({ICO_Status reason}; MATCH([Unique reference]@row; {ICO_Unique Reference})); IF(CONTAINS([Process Area]@row; "Payroll"); INDEX({ICO_Status reason}; MATCH([Unique reference]@row; {ICO_Unique Reference})); IF(CONTAINS([Process Area]@row; "Master Data"); INDEX({MD_Status reason}; MATCH([Unique reference]@row; {MD_Unique Reference})); IF(CONTAINS([Process Area]@row; "General accounting"); INDEX({GA_Status reason}; MATCH([Unique reference]@row; {GA_Unique Reference})); IF(CONTAINS([Process Area]@row; "Fixed Assets"); INDEX({FA_Status reason}; MATCH([Unique reference]@row; {FA_Unique reference}))))))); "")

The only dependencies for the formula to work are the Unique reference and the Process Area. Any thoughts on why this is happening? There's not a lot of data yet, so it should not be a lag issue.

Kelly Moore

Hey @Nero

I apologize, but I'm unclear what all is happening exactly with your Move Rows. We'll have to explore that next.

I had assumed that your formula was working as written then stopped working, so I didn't really inspect it. I just realized that the syntax of your Contains formula appears incorrect unless the syntax is different in different countries. The syntax I use is CONTAINS(search for, range). Swap the text portion and the @row reference. IF(CONTAINS("Payroll"; [Process Area]@row). Also, in your MATCH function, add a ;0 MATCH([Unique reference]@row; {ICO_Unique Reference};0) This is the [search type] and says your data is unsorted

Please make the changes to all of your formula - and ensure that you should get a match with the data you are searching for. Let me know

Kelly Moore

@Nero

ah. Yes, the sequencing of the refresh sometimes does pose a problem. In this case, there’s a different way of solving your problem. Smartsheet is already capturing that Created date for you. There will not be a sequencing problem with this date. Insert a new column and for column properties, select Auto-Number/System. When the picklist opens, select Created. Note- when moving or copying rows you can’t have multiple columns with the same names. You are allowed to change these names, just like any other column. To later use only the date portion of this timestamp,perhaps in formulas, you may need the function DATEONLY.

Long story, just use the Created system field for this first date

Kelly

Kelly Moore

Hello @Nero

I notice that the new data has an additional character in the Unique ID vs old data. Is the data in the source field also containing this additional character? If you remove the IFERROR portion of the formula, are you seeing any errors other than NoMatch?

Nero Naidoo

Hi KDM, thank you for your response. the unique reference is the same on both sheets, and if I remove the Iferror, the result is #No Match.

here's a sample of the source sheet.

Kelly Moore

As an experiment, copy a response of the new data from the source sheet Unique Reference and Process Area. Paste each into the target sheet. If you do it one field at a time you can see if a particular field is causing a problem. An extra or missing space will cause NoMatches to happen and they are difficult to catch. Also, verify that the {FA_Unique reference} in your source sheet includes the entire column and not a partial range of the old data set

Nero Naidoo

TRied that, still doesn't work. I picked another error as well and maybe its all linked.

My maid data sheet is completed through a form submission. I made copies of that sheet for the various resolver groups, ie. fixed asset, Intercompany etc. I have a workflow to copy rows from the main datasheet to the respective resolver based on the process area as per below. previously the row would copy but exclude the date, so I added in the date condition. now it copies an extra row with a unique ref with all the other rows being blank. GA088 does not exist in the main datasheet. I'm at a loss:(

Kelly Moore

Hey @Nero

I apologize, but I'm unclear what all is happening exactly with your Move Rows. We'll have to explore that next.

I had assumed that your formula was working as written then stopped working, so I didn't really inspect it. I just realized that the syntax of your Contains formula appears incorrect unless the syntax is different in different countries. The syntax I use is CONTAINS(search for, range). Swap the text portion and the @row reference. IF(CONTAINS("Payroll"; [Process Area]@row). Also, in your MATCH function, add a ;0 MATCH([Unique reference]@row; {ICO_Unique Reference};0) This is the [search type] and says your data is unsorted

Please make the changes to all of your formula - and ensure that you should get a match with the data you are searching for. Let me know

Nero Naidoo

Great!!!!! that works. Thank you so much@KDM. Now for the move rows.

On my main sheet, I have an automation rule to record the date when a new record is created, however, this sometimes takes a few seconds to update. So when the move row Automation occurs, it may move the row without a date. I tried adding a condition to the automation "when the date is not blank", but this breaks the process. What can I do? sorry for all the questions, I've been using smart sheets for 2 weeks now, so still learning.

Kelly Moore

@Nero

ah. Yes, the sequencing of the refresh sometimes does pose a problem. In this case, there’s a different way of solving your problem. Smartsheet is already capturing that Created date for you. There will not be a sequencing problem with this date. Insert a new column and for column properties, select Auto-Number/System. When the picklist opens, select Created. Note- when moving or copying rows you can’t have multiple columns with the same names. You are allowed to change these names, just like any other column. To later use only the date portion of this timestamp,perhaps in formulas, you may need the function DATEONLY.

Long story, just use the Created system field for this first date

Kelly

Nero Naidoo

Thank you Kelly that worked like a dream. Super⭐️