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.