Can't see what I am doing wrong

MEJ

Hello wonderful smartsheet users and apologies for a question that may not seem like it needs to be asked but I keep getting unparsable error for my cross sheet formula and i just can't see my error.

I am trying to have Program name entered on my target sheet if the status on primary sheet says "approved"… this is the formula I have that is not working

=IF({RFP Initial Application with Form Range 1)@ row= "Approved", {RFP Initial Application with Form Range 2}@row, ""))

please note the range 1 is linked to the status column of primary sheet and range 2 is linked from Program name column on primary sheet.

Thank you for your guidance and time.

jmyzk_cloudsmart_jp

Hi @MEJ

I am not sure if I understand your intention correctly, but if you want to populate Program names from another sheet where Status = "Approved", and display them in multiple rows or in a Sheet Summary field as a concatenated list, here are a couple of solutions you might try:

For multiple rows (one per approved entry)

If your target sheet has a helper column like [Row #] With values 1, 2, 3, ..., you can use the following:

=IFERROR(

  INDEX(

    COLLECT(

      {RFP Initial Application with Form Range 2}, 

      {RFP Initial Application with Form Range 1}, 

      "Approved"

    ), 

    [Row #]@row

  ), 

  ""

)

• {Form Range 1}: Cross-sheet reference to the Status column

• {Form Range 2}: Cross-sheet reference to the Program Name column

• [Row #]@row: A helper column with numeric values (1, 2, ...) to return the nth match

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

For summary field or single cell output (all program names concatenated)

Use this to combine all approved programs into one cell, separated by line breaks:

=JOIN(

  COLLECT(

    {RFP Initial Application with Form Range 2}, 

    {RFP Initial Application with Form Range 1}, 

    "Approved"

  ), 

  CHAR(10)

)

https://app.smartsheet.com/b/publish?EQBCT=0a6a477d37c140a79f46a03289909788 (Published Sample Sheet: Editable)

Note that the formula structure below may appear logical at first glance, but unfortunately, it won’t work as expected in Smartsheet. This is because:

=IF(
   {RFP Initial Application with Form Range 1} = "Approved", 
   {RFP Initial Application with Form Range 2}, 
    ""
)

• In Smartsheet, you can’t directly compare a whole range to a single value like "Approved" .
• So, the {range}="Text" is an invalid condition.
• Likewise, returning a range if the IF condition is met is not possible, as we can not place a range in a cell.

So, IF({Range1}="Text",{Range2},"") format works only when the ranges are pointing to a single cell, as shown below. However, if you are referencing an entire column, such as Status, the format does not work.

Isis Taylor

You have a parenthesis instead of a bracket at the end of your first range. That might be the problem.

MEJ

Changed to bracket and still am getting the same error…

Naeem Ejaz

You're on the right track — and don’t worry, this type of #UNPARSEABLE error is very common with cross-sheet formulas in Smartsheet! It's often due to syntax issues, especially with brackets, parentheses, and referencing logic.

=IF({RFP Initial Application with Form Range 1} = "Approved", {RFP Initial Application with Form Range 2}, "")

Issue	Fix
@row used incorrectly with cross-sheet ranges	✅ Remove it. Cross-sheet references return a range, not a row-specific value.
Mismatched parentheses	✅ Closed correctly now.
Unneeded second @row reference	✅ Removed for clarity and functionality.

How It Works:

• {RFP Initial Application with Form Range 1} → should be your Status column from the source sheet.
• {RFP Initial Application with Form Range 2} → should be your Program Name column from the same source sheet.
• This formula works only if both ranges are the same length and aligned row-for-row.

MEJ

Thank you for the guidance but still not working now with the changes getting "invalid operation" error.

jmyzk_cloudsmart_jp

Hi @MEJ

I am not sure if I understand your intention correctly, but if you want to populate Program names from another sheet where Status = "Approved", and display them in multiple rows or in a Sheet Summary field as a concatenated list, here are a couple of solutions you might try:

For multiple rows (one per approved entry)

If your target sheet has a helper column like [Row #] With values 1, 2, 3, ..., you can use the following:

=IFERROR(

  INDEX(

    COLLECT(

      {RFP Initial Application with Form Range 2}, 

      {RFP Initial Application with Form Range 1}, 

      "Approved"

    ), 

    [Row #]@row

  ), 

  ""

)

• {Form Range 1}: Cross-sheet reference to the Status column

• {Form Range 2}: Cross-sheet reference to the Program Name column

• [Row #]@row: A helper column with numeric values (1, 2, ...) to return the nth match

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

For summary field or single cell output (all program names concatenated)

Use this to combine all approved programs into one cell, separated by line breaks:

=JOIN(

  COLLECT(

    {RFP Initial Application with Form Range 2}, 

    {RFP Initial Application with Form Range 1}, 

    "Approved"

  ), 

  CHAR(10)

)

https://app.smartsheet.com/b/publish?EQBCT=0a6a477d37c140a79f46a03289909788 (Published Sample Sheet: Editable)

Note that the formula structure below may appear logical at first glance, but unfortunately, it won’t work as expected in Smartsheet. This is because:

=IF(
   {RFP Initial Application with Form Range 1} = "Approved", 
   {RFP Initial Application with Form Range 2}, 
    ""
)

• In Smartsheet, you can’t directly compare a whole range to a single value like "Approved" .
• So, the {range}="Text" is an invalid condition.
• Likewise, returning a range if the IF condition is met is not possible, as we can not place a range in a cell.

So, IF({Range1}="Text",{Range2},"") format works only when the ranges are pointing to a single cell, as shown below. However, if you are referencing an entire column, such as Status, the format does not work.

MEJ

@jmyzk_cloudsmart_jp Thank you for you thorough and clear explanation. Your response has allowed me to get the linking i was looking for by adding that helper Column. Thank you that was not an option i had thought of. I appreciate your time and your helpful while non judgmental input.

Thank you Community yet again for the assist. 🤩

jmyzk_cloudsmart_jp

Happy to help!😁 @MEJ