Index Collect with an IF and a Join?

Dave Mex

Good morning all

I'm after help with a complex formula concept. FYI these are not live data sheets just moc ups.

Data Base Sheet

Metric Sheet

Bellow is the formula im currently using to pull contracted orders across to a second Metric sheet.

=IFERROR(INDEX(COLLECT({DATA BASE | Customer}, {DATA BASE | Start Date}, >=DATE(2025, 1, 1), {DATA BASE | Start Date}, <DATE(2025, 2, 1), {DATA BASE | Status}, ="Contracted"), [Primary Column]@row), "")

What I want to achieve is to be able to also pull across the High Prob orders as well as the In review ones too. BUT i want to be able to add a "*" after the high Probs customer name, a "," after the In Review (But not high prob) and a "." after the contracted customer name. This would allow me to use conditional formatting to colour code the cells accordingly. Turning my metrics sheet into a live graph of the current order status.

So I was hoping this would be an easy wrap in an IF or use the IFERROR function but its currently falling over when i try that.

Any and all help would be much appreciated

jmyzk_cloudsmart_jp

Hi @Dave Mex

It's a good idea to add the "*",",", and "." to the customer's name for conditional formatting!

In the demo solution below, I added the [In Review] helper column to simplify the Metric Sheet's formula.

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

Then, I added the following IF clause to your formula;

[Jan] =IFERROR(INDEX(COLLECT({Data Base | Customer}, {Data Base | Start Date}, >=DATE(2025, 1, 1), {Data Base | Start Date}, <DATE(2025, 2, 1), {Data Base | Status}, "Contracted"), [Primary Column]@row) + IF(INDEX({Data Base | Hi Prob}, [Primary Column]@row) = 1, "*", IF(INDEX({Data Base | In Review}, [Primary Column]@row) = 1, ",", ".")), "")

or

=IFERROR(

    INDEX(

        COLLECT(

            {Data Base | Customer}, 

            {Data Base | Start Date}, >=DATE(2025, 1, 1), 

            {Data Base | Start Date}, <DATE(2025, 2, 1), 

            {Data Base | Status}, "Contracted"

        ), 

        [Primary Column]@row

    ) + 
IF(

        INDEX({Data Base | Hi Prob}, [Primary Column]@row) = 1, 

        "*", 

        IF(

            INDEX({Data Base | In Review}, [Primary Column]@row) = 1, 

            ",", 

            "."

        )

    ), 

    ""

)

• IF Conditions:
  • Check additional criteria for each row:
    • If {Data Base | Hi Prob} for the current row equals 1, appends "*" to the customer's name.
    • If {Data Base | In Review} for the current row equals 1, appends a "," (only if Hi Prob is not true).
    • Otherwise, appends "." for "Contracted" customers.

• String Concatenation (+):
  • Combines the customer name (from COLLECT) with the appropriate symbol based on the conditions above.

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

Dave Mex

Thank you @jmyzk_cloudsmart_jp for your help on this!

In the end i went with putting in another helper column that looked if the customer was HI Prob or Contracted then added the distinct mark to their name. meaning I could use a simple index collect on the new named column. Simple solution to a problem i made more difficult than I needed to!

jmyzk_cloudsmart_jp

Hi @Dave Mex

It's a good idea to add the "*",",", and "." to the customer's name for conditional formatting!

In the demo solution below, I added the [In Review] helper column to simplify the Metric Sheet's formula.

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

Then, I added the following IF clause to your formula;

[Jan] =IFERROR(INDEX(COLLECT({Data Base | Customer}, {Data Base | Start Date}, >=DATE(2025, 1, 1), {Data Base | Start Date}, <DATE(2025, 2, 1), {Data Base | Status}, "Contracted"), [Primary Column]@row) + IF(INDEX({Data Base | Hi Prob}, [Primary Column]@row) = 1, "*", IF(INDEX({Data Base | In Review}, [Primary Column]@row) = 1, ",", ".")), "")

or

=IFERROR(

    INDEX(

        COLLECT(

            {Data Base | Customer}, 

            {Data Base | Start Date}, >=DATE(2025, 1, 1), 

            {Data Base | Start Date}, <DATE(2025, 2, 1), 

            {Data Base | Status}, "Contracted"

        ), 

        [Primary Column]@row

    ) + 
IF(

        INDEX({Data Base | Hi Prob}, [Primary Column]@row) = 1, 

        "*", 

        IF(

            INDEX({Data Base | In Review}, [Primary Column]@row) = 1, 

            ",", 

            "."

        )

    ), 

    ""

)

• IF Conditions:
  • Check additional criteria for each row:
    • If {Data Base | Hi Prob} for the current row equals 1, appends "*" to the customer's name.
    • If {Data Base | In Review} for the current row equals 1, appends a "," (only if Hi Prob is not true).
    • Otherwise, appends "." for "Contracted" customers.

• String Concatenation (+):
  • Combines the customer name (from COLLECT) with the appropriate symbol based on the conditions above.

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

Dave Mex

@jmyzk_cloudsmart_jp This has worked perfectly! Taking the If out and making it its own formula was where i was going wrong! Thank you so much for your help.

Dave Mex

Hello All

Sorry I thought this had worked however it actually falls over as the IF's afterwards are not doing the indexing. So they are hitting the first returns in order that meet the criteria. Not the the first customer names that the first part of the formula dig out.

Also with further attempts Iv tried adding the data range and index collect to the IF's and all it does is stack the results. So if the first value has 1 response and the second would return 2 you would get the First one and then the second criteria would only return the 2nd response because the index is stacking. not consecutive.

Any Ideas? On how to make it work?

So what I currently have is

=IFERROR(
INDEX(
COLLECT(
{Data Base | Customer},
{Data Base | Start Date}, >=DATE(2025, 1, 1),
{Data Base | Start Date}, <DATE(2025, 2, 1),
{Data Base | Needed}, 1,
{Data Base | Hi Prob}, 1,
),
[Primary Column]@row
)

• "*",
  IFERROR(
  INDEX(
  COLLECT(
  {Data Base | Customer},
  {Data Base | Start Date}, >=DATE(2025, 1, 1),
  {Data Base | Start Date}, <DATE(2025, 2, 1),
  {Data Base | Needed}, 1,
  {Data Base | Hi Prob}, 1,
  ),
  [Primary Column]@row
  )
• ".",
  IFERROR(
  INDEX(
  COLLECT(
  {Data Base | Customer},
  {Data Base | Start Date}, >=DATE(2025, 1, 1),
  {Data Base | Start Date}, <DATE(2025, 2, 1),
  {Data Base | Needed}, 1,
  {Data Base | Hi Prob}, 1,
  ),
  [Primary Column]@row
  )
• "'", "")))
  

I also added in some extra columns to try and help rather than using "Contracted" As you can now see all the ones I want to be pulled across to the support sheet have a tick in the needed column.

jmyzk_cloudsmart_jp

Hi @Dave Mex

Using your helper columns, I added additinal helper columns to simplify the Metrics Sheet formula.

[In Review] =IF(AND(ISDATE([Date In Review]@row), Status@row <> "Contracted"), 1)
[Submitted] =IF(Status@row = "Submitted", 1)
[Contracted] =IF(Status@row = "Contracted", 1)
[Needed] =IF(Status@row <> "Rejected", 1)
[*] =IF(AND([Hi Prob]@row, Needed@row), 1)
[,] =IF(AND(NOT([Hi Prob]@row), Needed@row, NOT(Contracted@row)), 1)
[.] =IF(Contracted@row, 1)

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

Using the last three helper columns, the formula in the Metric Sheet becomes like this;

[Jan]

=IFERROR(INDEX(COLLECT({Data Base | Customer}, {Data Base | Start Date}, >=DATE(2025, 1, 1), {Data Base | Start Date}, <DATE(2025, 2, 1), {Data Base | Status}, "Contracted"), [Primary Column]@row) + IF(INDEX({Data Base | *}, [Primary Column]@row) = 1, "*", IF(INDEX({Data Base | comma}, [Primary Column]@row) = 1, ",", IF(INDEX({Data Base | .}, [Primary Column]@row) = 1, "."))), "")

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

It would be easier for you to get a copy of the demo sheets. If you want one, please contact me at jmiyazaki@cloudsmart.jp.

Dave Mex

Thank you @jmyzk_cloudsmart_jp for your help on this!

In the end i went with putting in another helper column that looked if the customer was HI Prob or Contracted then added the distinct mark to their name. meaning I could use a simple index collect on the new named column. Simple solution to a problem i made more difficult than I needed to!