Moving Rows to another sheet

ldavenportHAP

Hello!

I am looking for some assistance to see if this is possible. My company is utilizing SS for our staffs productivity. Currently it looks something like this

in order for my formula in the "From Previous Month" to work staff must remain in the exact same order as it is not a column formula example for "Sally Sue" in February

=IF(NOT(ISBLANK([Accounts Per Day]@row)), IF([Accounts Per Day]51 < [Accounts Per Day]@row, "Up", IF([Accounts Per Day]51 > [Accounts Per Day]@row, "Down", "")))

If anyone knows a way to make that a column formula id love to know

ANYWAYS...

what i would like to happen is if someone marks a staff member as "No" in the active column id love a way to automatically add No for all instances for that user and move it to another sheet. I understand how to make a single row move to another sheet but its there a way to automate "If admin marks 1 instance of "Sally Sue" as "No" in Active column, all rows with "Sally Sue" are moved to "Termed Staff Log". Not all leaders at my company are SmartSheet savvy so if i can make this process as easy as possible for them it would be appreciated!

Now that I write this all out I think even if i have rows move the formula for the "From Previous Month" will still need to be manually updated to reflect the new comparison row? idk. Any insight or suggestions are welcome. The "From Previous Month" is a really great quick visual way for our staff to know how they are doing and id hate to get rid of it even if its the bane of this sheets existence lol

Paul Newcome

When you copy or move a row, the data becomes static. No formulas will exist in the new sheet. So you shouldn't have to update anything after the row has been moved.

The initial move row issue should be solvable. Insert a hidden helper column with this column formula:

=IF(COUNTIFS(Staff:Staff, @cell = Staff@row, Active:Active, @cell = "No")> 0, "MOVE")

Then you can set up your move row automation to trigger when this helper column changes to "MOVE".

You can also make the [From Previous Month] column's formula more dynamic and we may be able to apply it as a column formula as well. Give this a try and let me know how it works for you:

Insert a hidden helper column (auto-number called "Auto").

Insert a hidden helper column (text/number called "Row") with this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then in the [From Previous Month] column you should be able to use this:

=IFERROR(IF(Leader@row <> "", IF(INDEX([Accounts Per Day]:[Accounts Per Day], MAX(COLLECT(Row:Row, Row:Row, @cell< Row@row, Staff:Staff, @cell = Staff@row))) < [Accounts Per Day]@row, "Up", IF(INDEX([Accounts Per Day]:[Accounts Per Day], MAX(COLLECT(Row:Row, Row:Row, @cell< Row@row, Staff:Staff, @cell = Staff@row))) > [Accounts Per Day]@row, "Down"))), "")

Paul Newcome

When you copy or move a row, the data becomes static. No formulas will exist in the new sheet. So you shouldn't have to update anything after the row has been moved.

The initial move row issue should be solvable. Insert a hidden helper column with this column formula:

=IF(COUNTIFS(Staff:Staff, @cell = Staff@row, Active:Active, @cell = "No")> 0, "MOVE")

Then you can set up your move row automation to trigger when this helper column changes to "MOVE".

You can also make the [From Previous Month] column's formula more dynamic and we may be able to apply it as a column formula as well. Give this a try and let me know how it works for you:

Insert a hidden helper column (auto-number called "Auto").

Insert a hidden helper column (text/number called "Row") with this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then in the [From Previous Month] column you should be able to use this:

=IFERROR(IF(Leader@row <> "", IF(INDEX([Accounts Per Day]:[Accounts Per Day], MAX(COLLECT(Row:Row, Row:Row, @cell< Row@row, Staff:Staff, @cell = Staff@row))) < [Accounts Per Day]@row, "Up", IF(INDEX([Accounts Per Day]:[Accounts Per Day], MAX(COLLECT(Row:Row, Row:Row, @cell< Row@row, Staff:Staff, @cell = Staff@row))) > [Accounts Per Day]@row, "Down"))), "")

ldavenportHAP

@Paul Newcome Thank you so much you are a genius!

Paul Newcome

Happy to help. 👍️

A C

@Paul Newcome Hi Paul, this answer is very helpful. But do you know of a way to copy only certain information from one sheet to another and have it continuously build on itself when specific criteria is met? Say I have columns 1 - 5 but I only need information from the cells in columns 3 & 4. Is this possible in smartsheet without using a formula that contains "Match" or anything to reference from the second sheet? Or do we need to use another program in conjunction with smartsheet like Power Automate in order to achieve our goal?

Paul Newcome

@A C The Copy Row automation will only pull the entire row. If you want to pull in only certain columns you will need either Data Mesh or you will need to use a 3rd party app.

Andrée Starå

Hi @A C

I hope you're well and safe!

To add to Paul's excellent answer/advice.

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

Would that work/help?

I hope that helps!

Have a fantastic weekend & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

A C

Thank you both for your helpful suggestions. They are greatly appreciated.

Happy New Year!

Andrée Starå

@A C

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!