Best Of

Re: SumIF formula help

Hi @Burger_DCYF,

This formula should work for you.

=IF(threshold@row = "yes", SUM([totalscore1]@row:[totalscore3]@row))

Hope this helps,

Dave

DKazatsky2

1 ·

Re: Getting started

Hi Gary,
Thanks a lot for your help. The links work perfectly.
Regards

Chantal HM

1 ·

Re: April Question of the Month - Join the conversation and receive a badge

I used to work for a private Catholic college in student financial services. One year, we had hired a consultant to completely overhaul our enrollment process for our online Master's program. She was a certified project management professional. It was a huge project that touched every area of student service and resulted in the creation of a brand new department with a cross-functional staff which I ended up managing. The tools, templates and techniques she used were textbook project management and peaked my interest enough to change my career path.

Isis Taylor

1 ·

Re: April Question of the Month - Join the conversation and receive a badge

My current position is 100% the product of my current boss. I was hired as her Executive Assistant, eager to learn, and took on opportunities outside my scope when needed. She has pushed me beyond what I thought I was capable of - including Smartsheet. She has championed everything I've done and taught me valuable lessons along the way, which led to me becoming Process Improvement Manager. Now that she is retiring in September, I'm ready for whatever new challenge may come my way.

Darla Brown

1 ·

Re: How to Preserve INDEX Formula Data When Archiving Row?

You can update your INDEX/MATCH with an IFERROR to look at the archive sheet if there is no match on the active sheet.

Paul Newcome

1 ·

Re: Alternative to unnecessary browse button change

I was thrilled to find this "enhancement" missing today. Appreciate how responsive Smartsheet is to community feedback. I bounce between dozens (and dozens) of workspaces, so doubling the effort every time I need to work on another client's project is silly.

Gwendolene Day

3 ·

Re: Updates to the Left Navigation, now generally available!

Thank you for the feedback. We heard you and we're taking action. In case you missed the update to this original post, we'll be rolling this feature back and returning to the browsing experience you were enjoying previously.

Arsineh

4 ·

Re: Extracting Multiple Data from one cell into multiple cells - same row

Happy to help. 👍️

Paul Newcome

1 ·

Re: Indexing from Multiple Sheets

Hi @tracy.heppes40761

You’re very close — your logic is correct, but the issue comes from how the formula is structured.

What caused the error

The original formula likely broke due to these points:

IFERROR only accepts 2 arguments
→ It cannot handle multiple fallbacks unless nested
Multiple conditions must be chained using nested IFERROR
→ IFERROR(IFERROR(...)) pattern is required
MATCH argument order must be correct
→ MATCH(lookup value, range, 0)
Parentheses were broken in the last part of the formula
→ Common when adding 3rd / 4th conditions

Problem pattern (for reference)

=IFERROR(A, B, C, D)

your formula, formatted:
=IFERROR(

	INDEX({AHM.Client}, MATCH([CU Project ID]@row, {AHMPlan.CUProjectID}, 0)), 

	INDEX({ahmne.client}, MATCH([CU Project ID]@row, {ahmne.cuprojid}, 0)), 

	INDEX({ahmse.client}, MATCH([CU Project ID]@row, {ahmse.cuprojid}, 0), 

	INDEX({ahmw.client}, MATCH([CU Project ID]@row), {ahmwe.cuprojid}, 0))

)

This is invalid in Smartsheet

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

Option 1: First match wins (INDEX + MATCH)

Please note this formula uses nested IFERRORs.

=IFERROR(

    INDEX({AHMPlan.client}, MATCH([CU Project ID]@row, {AHMPlan.cuprojectid}, 0)),

    IFERROR(

        INDEX({ahmne.client}, MATCH([CU Project ID]@row, {ahmne.cuprojectid}, 0)),

        IFERROR(

            INDEX({ahmse.client}, MATCH([CU Project ID]@row, {ahmse.cuprojectid}, 0)),

            IFERROR(

                INDEX({ahmw.client}, MATCH([CU Project ID]@row, {ahmw.cuprojectid}, 0)),

                ""

            )

        )

    )

)

Checks each sheet in order and returns the first match found

Option 2: Return all matches (COLLECT + JOIN)

Using JOIN(COLLECT()) makes the formula; If the COLLECT range is empty, the JOIN(COLLECT()) simply returns blank, making IFERROR unnecessary.

=JOIN(COLLECT({AHMPlan.client}, {AHMPlan.cuprojectid}, [CU Project ID]@row))

+ " "

+ JOIN(COLLECT({ahmne.client}, {ahmne.cuprojectid}, [CU Project ID]@row))

+ " "

+ JOIN(COLLECT({ahmse.client}, {ahmse.cuprojectid}, [CU Project ID]@row))

+ " "

+ JOIN(COLLECT({ahmw.client}, {ahmw.cuprojectid}, [CU Project ID]@row))

Returns all matches across all sheets

What my example shows

For example:

P011
- INDEX/MATCH → Client X
- JOIN/COLLECT → Client X Client X

👉 This means the same Project ID exists in multiple sheets

When to use which

INDEX/MATCH (nested IFERROR)
→ Use when each ID should exist in only one sheet
COLLECT/JOIN
→ Use when multiple matches across sheets are valid

Notes

JOIN version may produce:
- extra spaces
- duplicate values

But it’s much simpler and easier to maintain

You were on the right track — just needed proper nesting and cleanup

The Four Demo Source Sheets

Please note that P011 exists in both AHMPlan and ahmw sheets.

jmyzk_cloudsmart_jp

1 ·

Re: Pre-Populate a Form with Query String

Hi @alexis.ray89371

As the help article shows, if you want to include a space or another special character in your URL, you need to use URL percent-encoding.

Quarterly RPMT Survey - Smartsheet.com (You can open this published demo sheet to see the pre-population.)

I replaced the space, ".", and "@", and the form below shows the four fields are pre-populated as I expected. I suspected the emails with "." and "@" are the main culprits.

[Final Link]
=[Original Form Link]# 
+ SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE(
        "?Submitter Email=" + [EM Survey Contact Email Address]@row
         + "&Submitter Full Name=" + [EM Counterpart Full Name]@row
         + "&Region=" + Region@row
         + "&Role=" + [EM Role]@row
         + "&Quarter Reported=" + Period@row, 
        " ", "%20"),
    ".", "%2E"),
  "@", "%40")

As I tested, "," and "%2C" work as well. (I do not know why.)

Help Article

Use a URL query string to prefill default values on a form | Smartsheet Learning Center

If you want to include a space or another special character in your URL, you need to use URL percent-encoding. The following table lists a few common special characters and their percent-encoding values. These characters are also known as escape characters.

jmyzk_cloudsmart_jp

1 ·