INDIRECT(ADDRESS within SUMPRODUCT

[ImeoT]

Hi everyone,

I am trying to replace part of a formula to make it more dynamic.

To do this, I am replacing $B$10 with INDIRECT(ADDRESS(ROW(D15)-MOD(ROW(D15)+4,7),COLUMN(D15)-MOD(COLUMN(D15)-2,3)))

Now, ADDRESS(ROW(D15)-MOD(ROW(D15)+4,7),COLUMN(D15)-MOD(COLUMN(D15)-2,3)) = $B$10.

But replacing $B$10 in my formula does not work well for me.

Original formula:
=ISNUMBER(SEARCH("*text*",INDEX(Schedule!$F$2:$F$9998,SUMPRODUCT(($B$10=Schedule!$B$2:$B$9998)*($A$15=Schedule!$C$2:$C$9998)*($D$15=Schedule!$E$2:$E$9998)*(ROW(Schedule!$F$2:$F$9998)-1)),0)))

Original result:
TRUE

New Formula:
=ISNUMBER(SEARCH("*text*",INDEX(Schedule!$F$2:$F$9998,SUMPRODUCT((INDIRECT(ADDRESS(ROW(D15)-MOD(ROW(D15)+4,7),COLUMN(D15)-MOD(COLUMN(D15)-2,3)))=Schedule!$B$2:$B$9998)*($A$15=Schedule!$C$2:$C$9998)*($D$15=Schedule!$E$2:$E$9998)*(ROW(Schedule!$F$2:$F$9998)-1)),0)))

New Result:
FALSE

I would greatly appreciate any help with this, as I am pulling my hair out on this one!

 

[JustynaMK]

Hi ImeoT,

For sure one of our Excel gurus will be able to explain the whole subject properly, but for now I will try to give you an initial response.

1. SUMPRODUCT formula is an Array formula. Your "standalone" INDIRECT formula will evaluate to "$B$10" reference, while SUMPRODUCT will transform it to {"$B$10"}, thus returning an error.

2. As per Microsoft article (link below), a workaround has been described where you need to replace ROW with ROWS and COLUMN with COLUMNS.
https://techcommunity.microsoft.com...-SumProduct-with-Indirect-and-Row/td-p/141239

3. A potential solution is to replace your original INDIRECT formula with the following:
INDIRECT(ADDRESS(ROWS(D1:D15)-MOD(ROWS(D1:D15)+4,7),COLUMNS(A15:D15)-MOD(COLUMNS(A15:D15)-2,3)))

Please let me know if that works for you.

 

[ImeoT]

Hi JustynaMK,

Thank you so much for the very clear explanation and solution - it worked like a charm!

 

[JustynaMK]

No problem, glad it worked!

 

[ImeoT]

Sorry to be a bother, but I am now experiencing issues using the formula in a conditional formatting rule.

=ISNUMBER(SEARCH("*text*",INDEX(Schedule!$F$2:$F$999,SUMPRODUCT((INDIRECT(ADDRESS(ROWS(B$1:B3)-MOD(ROWS(B$1:B3)+4,7),COLUMNS($A3:B3)-MOD(COLUMNS($A3:B3)-2,3)))=Schedule!$B$2:$B$999)*($A3=Schedule!$C$2:$C$999)*(B3=Schedule!$E$2:$E$999)*(ROW(Schedule!$F$2:$F$999)-1)),0)))

seems to trigger all the cells in conditional formatting!

When input in the sheet next to the range tested, and dragged across rows and columns, it returns the right mix of TRUE/FALSE...

 

[JustynaMK]

Can you let me know what is the setup (rule/applied range) that you are using in your Conditional Formatting rule? What exactly would you expect to be formatted?

My initial thought is that you are applying your =ISNUMBER(...) formula to a range of cells (e.g. A1:A10). ISNUMBER(...) can only result in TRUE or FALSE, therefore your whole range (A1:A10) will be affected by the result of that formula.

 

[ImeoT]

You're correct, I am applying this to $B$3:$P$37.

Wouldn't the conditional formatting be checking on each cell the ISNUMBER statement and vary the non-fixed parts of the cell references?

Of note, only cell B10 should trigger a TRUE reply and therefore be formatted.
And the formatting still affects all cells even if I change B10 not to satisfy the test anymore.

 

[JustynaMK]

Good point - however, you will notice that the formula that we are currently using will always return $B$10 reference. Try stripping down INDIRECT part and insert your "original" ADDRESS function into any cell, and then drag it down & across. Even though we do have non-fixed parts and rows/columns cells are indeed changing, the result always defaults to B10.
=ADDRESS(ROWS(D1:D15)-MOD(ROWS(D1:D15)+4,7),COLUMNS(A15:D15)-MOD(COLUMNS(A15:D15)-2,3))

If you copy this formula from any other cell into cell $P$37 (the end of your range), you will notice that it also returns $B$10. Having that in mind, because your Conditional Formatting always evaluates this one range (B10), and because the result of this formula is TRUE, then the whole range is being formatted.

Edit:
A potential solution might be to modify your ADDRESS formula so that it "shifts" with every row & column.

 

[JustynaMK]

Of course I'm happy to help with modifying the ADDRESS appropriately but first I'd need to understand the original structure of your formula (i.e. why are you using these calculations, especially the MOD part).

 

[ImeoT]

Using ADDRESS(ROWS(B$1:B3)-MOD(ROWS(B$1:B3)+4,7),COLUMNS($A3:B3)-MOD(COLUMNS($A3:B3)-2,3)) , which is what I input in the conditional formatting, the values change appropriately from $B$3 to $B$10, $B$17, $B$24, $E$3, $E$10...

Essentially, I have a table in the Schedule sheet that lists events with dates and times.
The $B$P range is divided in 7 x 3 rectangles, which each represent a day, with each row being a time slot, and the possibility of 3 events for each time slot.
The first row of each rectangle contains the date. Column A contains times.

For each cell in that day/rectangle, I want conditional formatting to be triggered where a row in the Schedule table is found where:
1) The day matches the value in the top-left hand corner of the 7 x 3 area;
2) The time matches the value in column A; and
3) The event name matches the value in the current cell.

The MOD is helping me find the top-left hand corner cell.