How to interchange one cell value with another cell value in the same sheet.

moumita77

I have 2 sheets 1st is Asset Master sheet and 2nd is Material Movement sheet. Once I received a new cell value from Current Location Final which is populated from Material Movement sheet. I want to interchange the cell value in "Current location Final" to "Current location" in Asset Master sheet.

For e.g. After using the Index Match formula from Material Movement sheet, I received Congruent Global value under the "Current Location Final". Now I want to move the value of "Current location final" i.e. "Congruent global" value to "Current Location" in place of "My Home". Wanted to do it via automation or formula rather than using manual cut and paste.

Kindly guide on this.

Jake Gustafson

I'm not sure an automation workflow will work since the values you're trying to populate are dynamic based on that other sheet. The change cell value action wouldn't know what to replace the value with each time. Additionally, a formula makes it a little tough as well, because initially, your [Current Location] values are manually input I'm guessing, so having a formula in that column would impact the useability.

I don't think I've provided much assistance, but now if someone else comes swooping in with a solution, then I'll be in the loop. Maybe what I've said here will get the creative juices flowing for you or others to come up with a solution.

moumita77

True @Jake Gustafson I am putting down the current location value manually, but wanted to get the updated value once the Current location final value is populated using index formula from different sheet.

moumita77

Hey @Genevieve P. & @Paul Newcome can you guys guide on this case.

Paul Newcome

I would suggest a separate sheet that records the Current alongside a unique identifier (think form entries with new entries going to the top).

From there the sheet you have above can use an INDEX/COLLECT for both current and previous.

=INDEX({Entry Sheet Location Column}, {Entry Sheet Unique ID Column}, [Unique ID]@row), 1)

The above would be for the most recent or "Current" entry. For the previous entry, you would change the 1 at the end to a 2.