Sheet automations and cell updates

ghammar

We have a recruiting team for our clients. When the client requests us to fill a job role, they can specify one or more jobs in that role. When the recruiter fills one job, I copy the row to another sheet for billing, then I want to reset five key fields and decrement the number of jobs to fill. Does anyone have any suggestions for decrementing the counter? The Change Cell Value option only provides for a fixed text field. I tried using a formula and it dutifully copies the formula into the cell as text.

jmyzk_cloudsmart_jp

Hi @ghammar

A solution I came up with is to count the rows that match the source sheet.

In the image below, the [Filled Jobs] column uses the following formula to count the rows in the copy target sheet, Sheet 2, with the same Client Name and Job Role.

=COUNTIFS({Sheet 2: Customer Name}, [Client Name]@row, {Sheet 2: Job Role}, [Job Role]@row)

Then, instead of updating the Jobs to Fill, I added the Updated Jobs to Fill column and subtracted the Filled Jobs value from the Jobs to Fill.

https://app.smartsheet.com/b/publish?EQBCT=0f0f96fe905d44bd9645f43d470b77ea

As for copying the row when a Recruiter is Assigned, I created workflow automation to copy the rows when the Latest Recruiter Assigned value is changed. You could make the column a dropdown list to choose the Recruiters.

Copy row workflow automation.

Copy row target sheet

https://app.smartsheet.com/b/publish?EQBCT=b639f4f8a3804f5ea64dcfa9547117c1

jmyzk_cloudsmart_jp

Hi @ghammar

A solution I came up with is to count the rows that match the source sheet.

In the image below, the [Filled Jobs] column uses the following formula to count the rows in the copy target sheet, Sheet 2, with the same Client Name and Job Role.

=COUNTIFS({Sheet 2: Customer Name}, [Client Name]@row, {Sheet 2: Job Role}, [Job Role]@row)

Then, instead of updating the Jobs to Fill, I added the Updated Jobs to Fill column and subtracted the Filled Jobs value from the Jobs to Fill.

https://app.smartsheet.com/b/publish?EQBCT=0f0f96fe905d44bd9645f43d470b77ea

As for copying the row when a Recruiter is Assigned, I created workflow automation to copy the rows when the Latest Recruiter Assigned value is changed. You could make the column a dropdown list to choose the Recruiters.

Copy row workflow automation.

Copy row target sheet

https://app.smartsheet.com/b/publish?EQBCT=b639f4f8a3804f5ea64dcfa9547117c1

ghammar

@jmyzk_cloudsmart_jp Thanks! That works. I have to get used to using a lot of helper columns with Smartsheet.

jmyzk_cloudsmart_jp

Happy to help!😁