Complex formula using INDEX MATCH to update other columns

roya

Hello Smartsheet Community,

I am trying out how I can do an Index Match to look up data in column 1 and see if the data exists in column 2. If the data exists then update other columns ( like status , health and % Complete, Greatest milestone met) with Values

Im truly in a time crunch , and will be super appreciative if some one has any tips . My data is as below

Logic: Look up and see if value in column1 (Lineage) exists in Column 2 (Type), 

if it does then update the Column 3( Dev Complete Status) = " Complete"

Update column 4 ( Dev Complete %)to 70%,

Column 5 ( Health)="GREEN" and

mark the Greatest Milestone "Lineage Complete"

The greatest Milestone is a multi-select column with values

"Analysis", "Dev", "Test", "Lineage complete", "Migrate to QA", "Migrate to Prod"

Kelly Moore

Hey @roya

I think this approach will get you what you need. From a formula, you cannot have a formula sit in one column and cast a result to another column - the formula must reside in the column hat is being changed. Be aware, as is any column formula, this formula will overwrite any existing data that may be present in those cells. One cannot have manual data and automatic data exist simultaneously unless you're going in manually and adjusting cells. If you already have formulas in these columns, we can typically build onto those to account for this specific condition

Dev Complete Status

=IF(COUNTIFS(Lineage:Lineage, Type@row) > 0, "Complete")

Health

=IF(COUNTIFS(Lineage:Lineage, Type@row) > 0, "Green")

Greatest Milestone

=IF(COUNTIFS(Lineage:Lineage, Type@row) > 0, "Lineage complete")

Dev Complete %

=IF(COUNTIFS(Lineage:Lineage, Type@row) > 0, 0.70)

If, however, you can't overwrite all the cells then you must turn to automation and use the CHANGE CELL VALUE workflow. It will require a helper column added to your sheet. As with most helper columns, you can shove it to the right and hide it once it's built. Since you will need to trigger the automation to get all of the existing data in your sheet 'triggered' we will have to build this disjointedly so when you add the formula and save, all of your existing data will be 'triggered'. Going forward the automation would run automatically. These may seem like a lot of steps - it's just that I tried to break down the instructions into very granular actions.

First, add a helper checkbox column. Don't hide it yet, we will need to come back to this. You can name it whatever - I will call it Trigger Automation. Save your sheet. We will be building 4 automations - one for each column change. This may sound intimidating if you haven't dealt with automations before but you will find smartsheet has made this process very straight-forward.

Go to Automations

Choose Create Workflow from Scratch then follow this guide. Best practice is always to Title your Workflows - This one tells you which column we're tackling first.

Choose the helper column you created - Note I named mine "Trigger automation". That is a column name, not an action.

Once I scroll down, I can select different workflow actions. We want to choose Change Cell Value. Once selected, a dialogue box will pop up and ask us what column we want the action to occur to and what is the cell value we're inserting. When we duplicate this automation to create the other workflows, we will be changing these columns and these values (in addition to our workflow titles)

For this workflow we want to select [Dev Complete status] as the column and Complete as the cell value. Quotes are not needed when entering free text. If the cell is a dropdown, the selections will be available for selection.

Don't forget to SAVE the workflow.

You can either start each of the four flows from scratch or you can duplicate this one and edit as needed, once the workflow is saved. You must complete all 4 workflows before going back to the sheet and entering the formula into the helper column. As soon as you enter the formula into the helper and save, your workflows will execute.

Go back to helper checkbox column and insert this formula into a cell. It says if you find a match, check the box.

=IF(COUNTIFS(Lineage:Lineage, Type@row) > 0, 1)

Make this a column formula by right clicking into the cell with the formula, and scrolling to bottom of cell menu. Choose Convert to column formula. SAVE!!

This should put a checkmark in all required rows. After a few seconds or so, you should see the sheet telling you to refresh. Your results should be present.

Kelly

roya

Hello @Kelly Moore

First a million thanks for your detailed answer and all the time taken to advise me. I am excited at implementing and trying out , and will post my comments, after , to tell you how it went

Truly Truly very much appreciated!!

THANK YOU!!

Kelly Moore

Hey

My pleasure. If you have any trouble implementing then reach out and we'll work it together.

Kelly