Best Of

Hi @k.platt

This can be solved with a simple trick - counts two statuses separately and add them to get her:

=COUNTIFS({121 Task Status}, "Not Started", {121 Task Hierarchy}, "Child", {121 Finish Date}, <=TODAY(+14)) + COUNTIFS( {121 Task Status}, "In Progress", {121 Task Hierarchy}, "Child", {121 Finish Date}, <=TODAY(+14))

Let me know if this works?

Good Luck!

Greetings,

If you are trying to create a date 1 year in the future based on an approval date, I'm guessing you already have a column on your sheet for review date, correct?

If so, I would use a formula to calculate the review date in the column and not need a workflow. The formula would calculate the date 1 year in the future based on the request approved date. Using the column formula option would populate the formula to the entire column.

An example: =IFERROR(WORKDAY([Request Approved]@row, # of days in the future, holiday list if available), " ")

The formula will create a date in the future based on working days (excludes Saturday and Sunday). If you have a list of other holidays you can use that list in the formula.

Based on a start date of today there are aprox. 250 working days until next year. Your days may vary but wanted to provide a starting point.

I hope this helps.

Hi @Malorie Scaramozi

It sounds like an INDEX(MATCH will work for you, as long as the address is exactly the same in both sheets!

An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))

You'll notice that the Match requires two values separated by a comma, instead of how you have a ) between the Address and {range}.

Your syntax is just a little off, try this:

=INDEX({sheet names Range 1}, MATCH(Address@row, {sheet names Range 4}, 0))

Let me know if this works for you!

Cheers,

Genevieve

Hi @PKotha

I hope you're well and safe!

To add to Amit's excellent advice/answer.

Another option (depending on your structure and specifics) would be to only use cross-sheet formulas (VLOOKUP or INDEX/MATCH), so there is no need to copy the rows.

You'd use an auto number column in the source sheet and then add a so-called helper column in the destination sheet where you'd add the number manually, and we use that to connect the rows.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

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!

HI @PKotha

Unfortunately, when you apply copy/move automation, all the columns are moved to the destination. Here is a workaround that I would propose.

1. Create an automation which copies all the new rows (including all the columns) into the destination sheet.
2. All the columns in the destination sheet, except the primary key should be hidden.
3. Create new columns with same name as the hidden columns (with some variation as Smartsheet does not allow duplicate column names). You can limit the new columns to only those which you desire to be shown on the second sheet.
4. In these columns, apply INDEX/MATCH formula to reference data from the source sheet based on the primary key.

I hope this helps.

Best Regards

Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA

https://www.linkedin.com/in/amitinddr/

Hello JB - I am not aware of way to restrict users from the entry, unless you want to lock the column and turn it into a formula, so that the box checks itself after certain criteria are met. If that's not possible, another idea is to set up an automation that sends an email if the box is checked prematurely.

Hoping this helps,

Scott

@JennV Why exactly do you have 3 = "Complete", 3 = "Cancelled"? If those are not part of the text within the cell then you would need to remove those bits.