Move a Row Based on Information in a Different Row

BB2791

Hello,

I have a sheet with all the computers in our department. When they reach an expiration date I created a form for someone to enter the replacement computer's details. There is a field on the form to select the "Old Host Name" of the computer that is being replaced. That's the only identifier I can use to link the two together.

Upon submission I'll have a row that has the new computer's details and the old host name. The status will be "Current." I need to create an automated way to pick up the fact that the row of the original entry for the old computer with the "expired" status should be archived and moved to another sheet. I don't have to have the expired computers and the replacement ones in the same sheet but I can't simply get rid of the expired ones unless they have been replaced.

I am not sure of the best way to accomplish this and would appreciate any ideas!

Paul Newcome

You would want to do a COUNTIFS to count how many rows have [Host Name]@row in the [Old Host Name]:[Old Host Name] range. Then you would use the IF to say that IF that COUNTIFS is greater than zero, then flag the row.

Paul Newcome

Are you able to provide a screenshot for context? It sounds to me like you could use an IF/COUNTIFS to flag a row if the [Old Name] field is equal to the [Name] field then set up a move row automation triggered by the flag column.

BB2791

@Paul Newcome please see below:

The red row needs to be auto-archived (moved to another sheet) since the green row is the new replacement computer. The indentifier indicating which row should be archived is in the "Old Host Name" field. I tried out using a formula in line with what you're suggesting but I didn't get it quite right so if you can help me write it out to see the proper construction that would be great. Thanks!

Paul Newcome

What was the formula you tried?

BB2791

I tried this:

=IF([Old Host Name]@row = [Host Name]:[Host Name], "archive", "")

It's giving me an "invalid operation" error

Paul Newcome

You would want to do a COUNTIFS to count how many rows have [Host Name]@row in the [Old Host Name]:[Old Host Name] range. Then you would use the IF to say that IF that COUNTIFS is greater than zero, then flag the row.

BB2791

This is perfect. It works. Thank you!

Paul Newcome

Happy to help. 👍️