Update request - Reference a specific cell or row

MSGFreebird

Update request - When building a managed workflow for an update request it would be nice to be able to select a specific Cell or row that is to be changed not the cell on the row added by the update request.

For example. I have a sheet that individual populates with data using an intake form into (Row1). The assigned resource for that sheet creates daily update entries that start at row11. At some point that sheet needs to transfer to a new resource. I have created a workflow that triggers of a checkbox being checked that sends an email update request to the currently assigned resource. When the current resource changes the makes the change the change is not happening on Row1 it happens on the row the checkbox that triggered the update request. Which results in the assigned resource is not changed and the rest of the managed workflows never get sent to the correct individual.

Paul Newcome

You would need to add a formula to row 1 so that the box gets checked on row 1 when the lower row gets checked and then send your update request from there. You will also need to include a manual checkbox column there on row 1 where you check that box so you can filter out the lower row's checkbox.

So basically a manual entry checkbox column that is checked only on row 1 and then a formula in row 1 that will check the box when the lower box you are currently triggering from gets checked.

Your update request would be set up the same way to trigger when the same box gets checked, but you would include a condition of this manual checkbox being checked.

MSGFreebird

@Paul Newcome, Thanks Paul. I don't suppose you have an example you can share? The only problem I see with your idea is that Row 1 with data does not exist until the intake form is submitted. The default Row1 is moved down and becomes Row2.

Paul Newcome

I must have misunderstood your structure. I was under the impression that the changing/added rows were going at the bottom and you were always trying to send to the top row.

Are you able to provide some screenshots for context to your original post?

MSGFreebird

Good morning @Paul Newcome

Ok so here are screenshots to help build a better understanding.

Project DUL Sheet template. Hopefully it is not to small to read. If it is please let me know.

Intake form

Project Update Form

Possible solution.

Workflow

Hope that helps Sir.

Thank you for all your assistants.

Paul Newcome

You can use a formula there in row 1 to pull from the other rows based on the checked box. It would be an INDEX/MATCH. I believe the value to search for in the MATCH portion when using a checkbox is true (no quotes).

=INDEX([Assigned TE]:[Assigned TE], MATCH(true, [HO-meta]:[HO-meta], 0))

This will bring whatever row is checked to the top row which you can use a manual checkbox column to check only this row. Once the data is in the top row, your automation would be set up as usual and you would include a condition that the manual check column is checked.

MSGFreebird

Hey @Paul Newcome

So moving the update row that has the HO-Meta checked to the top is not possible with the way the data is populated. If the stated row is moved to the top, all of the other contacts will not be populated that are required for the other Managed Workflows to function correctly.

The best solution in my opinion is to be able to have the workflow run against a specific row instead of the row added.

In my solution the Row1 data will always be in existence before any update is logged and all of the contacts are required to be in row1 for all of the workflows to function as designed.

Paul Newcome

Right. I am not saying to move the checked row up. I am saying to use a formula in the top row to pull from whichever row is checked.

If you are just trying to pull in the most recent row with new rows added to the bottom, we can ignore the check and use a COUNTIFS function to tell the INDEX function which row to pull from.

In any event, the top row doesn't move, and none of the bottom rows need to move. We just use formulas to grab the checked row and copy the data in it to the top row.

MSGFreebird

Thanks @Paul Newcome , Sure wish there was a way to have a meeting as the back and forth is getting confusing. LOL.

MSGFreebird

Good morning Paul, @Paul Newcome

I am trying to figure out how to apply a formula to a row as you suggested above. Can you provide details or point me to where instructions are to accomplish this? As far as I know formulas can only be applied to cells and columns.

I have tried putting the formula "=INDEX([Assigned TE]:[Assigned TE], MATCH(true, [HO-meta]:[HO-meta], 0))" you posted above in the AT-Meta cell and creating a column formula out of it. Thinking maybe that would then copy the AT-Meta contact name in the update row, but because the column is formatted as "Contact" it appears the column formula option is not available.

Thanks

Gregg

Paul Newcome

The column type shouldn't impact whether or not it can be a column formula.

I thought you were only trying to send to a single row though? Not all rows?

Paul Newcome

In the below screenshots, [Send This Row] is manually checked so that we can include it as a condition. A through C are using the INDEX/MATCH to pull from the gray table (table is new rows created by form entries) based on the box being checked in the [Pull This Row] column.

The [Pull This Row] column is basically your [HO-meta] column.

MSGFreebird

@Paul Newcome

Ideally I would like to copy the AT-Meta cell of the update row and overwrite the AT-Meta cell in Row1.

MSGFreebird

@Paul Newcome

It sounds like what I am wanting to accomplish is not possible which makes everything I have done a waste. As I do not want the updater to have to open the sheet and manually change the AT-Meta contact in Row1 manually.

Paul Newcome

I must be missing something. In your screenshot you want to pull from a different section but in your last comment it sounds like you are pulling from the same column? Based on your screenshot, you want to pull from the checked row of [Column B] and output in the top row of [Column A] (column names for example only).

MSGFreebird

@Paul Newcome, Paul is there anyway to talk to someone about this? Trying to put it all in screenshots and texts seems to be mudding the water of understanding.