Column Formula to populate a Helper Column

Annaleah Morrow

EDIT: I just realized this is listed in the Add Ons and Integrations section rather than elsewhere, but I cannot move it.

I have created separate helper columns to return the value in [Plan Start Helper] for any of the matching [Opp #] where [Plan] contains the word "Plan" (I have other helper columns that need to search for Test, Architect, etc. The [Plan] column has spaces and may have more than one word.

Right now I am getting the expected 01/06/2025 for rows 1-8 (same Opp group), but when I go to rows 9-17, I expect to get a return of 07/15/24 for all rows, but I'm only getting that return on row 13 (which has "plan" in the [Phase]column @row), and returning 02/24/2025 for all other cells in that column for that group Opp # even though that is not a date associated with rows 9-17 at all. 2/24/25 continues into the next group as well. I have included a sample of the first three groups, based on OPP #.

Any ideas would be welcome!

Column Formula:

=IF(CONTAINS("Plan", Phase@row), [Start Date]@row, IF(COUNTIF([OPP #]:[OPP #], [OPP #]@row) > 0, INDEX([Start Date]:[Start Date], MATCH("Plan", COLLECT(Phase:Phase, [OPP #]:[OPP #], [OPP #]@row), 0)), ""))

Carson Penticuff

I am a little confused by your request/formula. First off, this is what it looks like you are trying to accomplish, see if it works for you. If it does, I guess you can ignore my questions below.

=IF(CONTAINS("Plan", Phase@row), [Start Date]@row, INDEX(COLLECT([Start Date]:[Start Date], [OPP #]:[OPP #], [OPP #]@row, Phase:Phase, CONTAINS("Plan", @cell)), 1))

If this isn't what you are looking for, here are the things I am a little confused about:

"where [Plan] contains the word "Plan""

Did you mean [Phase] instead of [Plan]? There is no [Phase] column in your screenshot, and the formulas you listed does not reference it either.

IF
  (
    COUNTIF([OPP #]:[OPP #], [OPP #]@row) > 0,
……

This condition will always return true, as it is impossible to return 0, so I'm not sure of the function of this IF statement.

Hopefully this helps.

Annaleah Morrow

Hi, @Carson Penticuff . Thank you for your analysis!

You are spot on. Sorry, in my screenshot it shows [Phase] is the column header, and "Plan" is the word I'm looking for. Interestingly, your formula worked like a charm for most of the groups of matching [Opp #], but there are some when I spot checked through the first 300 or so lines that are still wrong. Any idea what would be pushing these off? I would expect that all of the [Plan Start Helper] in these matching [Opp #] groups have the highlighted [Start Date] listed in the [Phase] column containing "Plan" at that row.