Best Of

Hi @WinaHath you have the same problem as Shubha did. You have too many arguments in your Collect statement. That's what that error means. Are you trying to find the first match for the Department Code in another sheet, where Approval Sequence=1?

If so, then don't use ,0 anywhere. I'm going to assume you want the first match in the Collect, so try

=INDEX(COLLECT({Approved By}, {Department Code}, [Department Code]@row, {Approval Sequence}, 1),1)

Hi @CamSME

I hope you're well and safe!

Unfortunately, it's not possible now, but it's an excellent idea!

Please submit this as a Product Feedback or Idea (If it hasn't been added already) when you have a moment.

Here's a possible workaround or workarounds

• Using a 3rd-party service like Zapier or similar
• Using the Smartsheet API

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!

Column formulas cannot reference a specific row number. All cell references must be "@row" references.

Try putting the date in a Sheet Summary field and referencing that instead or putting the date on every row and using "@row" references.

Date on every row:

COUNTIFS({part 3 #}, [Part Number]@row, {status}, OR(@cell = "Repaired", @cell = "Beyond Repair"), {rma closed date}, <=Date@row)

Date as Sheet Summary Field:

COUNTIFS({part 3 #}, [Part Number]@row, {status}, OR(@cell = "Repaired", @cell = "Beyond Repair"), {rma closed date}, <=Date#)

Yes. You still need the "@cell" even when referencing another sheet because you have already established the range.

Remember that the syntax for the COUNTIFS function is COUNTIFS(range 1, logical statement 1, range 2, logical statement 2, etc etc etc.

{status}, OR(@cell = "Repaired", @cell = "Beyond Repair (BER)")

Here, {status} is the range, and the entire OR statement is the logical statement. @cell is referencing back to {status} in the range.

Hi @Krys West - this is a good thought. Our organization has explored this before with a few other projects and at the time couldn’t find a way to make this happen.

You can add new versions of the document over top of previous versions - which solved our use case (but all other versions are still accessible if looking through the doc version history).

Hey @Jeff Reisman ,

I found a Plan B to combine my new need and what you offered that I needed. Here are my formulas:

Auto Auto Numbering

Parent =IF(Helper@row = "Parent", Auto@row)

ID =IFERROR(RANKEQ(Parent@row, Parent:Parent, 1), "")

Helper - Last column to the very right, this is a Text field.

I hid the columns that are not relevant. I added the INDEX/MATCH formula to the Blue highlighted Cells in the Release column. This first screenshot is all formulas with exception of the final (Helper) column, which is in text and highlighted in Purple. The second sheet is my Release List. Rather than manually entering each Release into the first screenshot, the Release List lets me have a master list of releases, and the first sheet is updated completely by formula! Having that purple column in text is a small price to pay for the automation!