Reference row about in Index?

LauraD

I have a stagnant formula that color codes projects the same if they are releasing the same week. I want to make the formula dynamic though for when projects move. I've used some other's solutions from this forum but still unable to get this to work. I have a large spreadsheet with projects and a Release Date column. I also have several helper columns:

Auto: this is automated numbering

Row: =MATCH(Auto@row, Auto:Auto, 0)

Week Color: Where my problematic formula lives and is connected to Conditional Formating. "Yes" result is one color, "False" is a second color, and "True" is a third color

My old formula in Week Color was this:

=IF(WEEKNUMBER([Release Date]2) = WEEKNUMBER(TODAY()) + 1, "Yes", IF([Release Date]1 = [Release Date]2, [Week Color]1, IF([Week Color]1 = "False", "True", "False")))

The formula I'm trying to get to work is this:

=IF(WEEKNUMBER([Release Date]@row) = WEEKNUMBER(TODAY()) + 1, "Yes", IF(INDEX([Release Date]:[Release Date], Row@row - 1) = [Release Date]@row, INDEX([Week Color]:[Week Color], Row@row - 1), IF([Week Color]1 = "False", "True", "False")))

Any help would be hugely appreciated!

Jeff Reisman

@LauraD

#BLOCKED error

Cause: The calculation is blocked because at least one of the cells referenced by the formula has an error.

Resolution: Determine which cell referenced by this formula contains an error, which will be more descriptive of the problem.

Let's break down the formula and see if we can find the culprit:

=IF(WEEKNUMBER([Release Date]@row) = WEEKNUMBER(TODAY()) + 1, "Yes",

If the Week number of the release date on this row = next week, "Yes"

IF(INDEX([Release Date]:[Release Date], Row@row - 1) = [Release Date]@row, INDEX([Week Color]:[Week Color], Row@row - 1),

If the Release Date on the row above this one is equal to the Release Date on this row, then make this row's week color the same as the row above it.

IF([Week Color]1 = "False", "True", "False")))

If the Week Color value on the first row of the sheet is "False", then set this row's week color to "True", otherwise set it to "False".

Based on this, it looks like [Week Color]2 should equal "True". But I've seen these types of formulas, having dependencies on rows above them, fail like this.

Problems also occur when a formula contains a reference to the column range that the formula is in. To counter this, one thing I would try is adding a helper column "WeekColorHelp" as the one to evaluate for the week color. Use this formula in the helper column:

=IFERROR([Week Color]@row, "")

Replace your INDEX([Week Color]:[Week Color], Row@row - 1) reference with INDEX([WeekColorHelp]:[WeekColorHelp], Row@row - 1) and see if that helps things.