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]

Thanks for explaining.


If I understand correctly, you would like to get something like this:

Excel 2013 32 bit
	A	B	C	D	E	F	G
1	13/11/2018
2	4:00 PM
3	Event A	07/11/2018	07/11/2018	07/11/2018	08/11/2018	08/11/2018	08/11/2018
4	4:00 AM	Event X	Event X	Event X	Event X	Event X	Event X
5	8:00 AM	Event X	Event X	Event X	Event X	Event X	Event X
6	12:00 PM	Event X	Event X	Event X	Event X	Event X	Event X
7	4:00 PM	Event X	Event X	Event X	Event X	Event X	Event X
8	8:00 PM	Event X	Event X	Event X	Event X	Event X	Event X
9	12:00 AM	Event X	Event X	Event X	Event X	Event X	Event X
10		12/11/2018	12/11/2018	12/11/2018	13/11/2018	13/11/2018	13/11/2018
11	4:00 AM				Event C	Event A	Event A
12	8:00 AM				Event C	Event A	Event A
13	12:00 PM				Event C	Event A	Event A
14	4:00 PM				Event A	Event B	Event C
15	8:00 PM				Event C	Event A	Event A
16	12:00 AM				Event C	Event A	Event A
Sheet1

Following this concept, if your whole "table" is presented in cells B3:P37, and your time slots are presented in cells A4:A37, then you can apply the following Conditional Formatting rules:


YELLOW fill color - will highlight the 7x3 rectangle which matches the input date present in cell A1.
Formula: =INDEX(B:B,ROUNDUP((ROW()-ROW(B$2))/7,0)*7-4,1)=$A$1
Applies to: =$B$3:$P$37


BLUE fill color - will highlight exactly one cell which matches the input date present in cell A1, time slot (cell A2) and Event name (cell A3).
Formula: =AND(INDEX(B:B,ROUNDUP((ROW()-ROW($A$2))/7,0)*7-ROW($A$4),1)=$A$1,$A3=$A$2,B3=$A$3)
Applies to: =$B$3:$P$37


However, please make sure that your BLUE Conditional Formatting rule is the first rule in the queue.

Let me know if this is what you're looking for.

 

[ImeoT]

Sorry for the delay getting back to you on this - my computer has been acting up.

Your explanation makes sense, but I need it integrated in a formula that also finds the date, time & event.

My raw data is in Worksheets("Schedule"):

	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	Date	Time Start	Time End	Game	Attendees	Attendees #	Repeat	Min	Max	Length	Viability
2	2	5/11/2018	6:00 PM	6:30 PM	Event A	Alex	1	1	3	6	0h7m0s	No
3	3	5/11/2018	7:00 PM	8:00 PM	Event B	Alex, Bob	2	3	2	4	1h0m0s	OK
4	4	5/11/2018	7:00 PM	8:00 PM	Event A	Bob, Chloe	2	8	1	8	0h56m0s	OK
5	5	9/11/2018	7:00 PM	7:30 PM	Event C	Chloe, Alex	2	1	2	5	0h30m0s	OK

<tbody>
</tbody>

Schedule​

I want to change the font for each cell in the following calendar, where Alex is an Attendee for the event taking place at the specified date and time.

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		November 2018
2		MONDAY			TUESDAY			WEDNESDAY			THURSDAY			FRIDAY
3		29			30			31			1			2
4	5:00 PM
5	5:30 PM
6	6:00 PM
7	6:30 PM
8	7:00 PM
9	7:30 PM
10		5			6			7			8			9
11	5:00 PM
12	5:30 PM
13	6:00 PM	Event A
14	6:30 PM
15	7:00 PM	Event B	Event A	Event C
16	7:30 PM

<tbody>
</tbody>

November 2018
​

As mentioned before, there is some issue with the formula:
=ISNUMBER(SEARCH("*Alex*",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)))

Excel 2013/2016

	B*	C*	D*	E*	F*	G*	H*	I*	J*
3	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
4	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
5	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
6	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
7	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
8	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
9	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
10	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
11	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
12	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
13	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
14	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
15	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
16	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

November 2018​

It also dawned on me that, using a shared workbook, my VBA code won't be able to add new conditional formatting, so I will need to rely on similar catch-all formatting for some similar testing of the event viability. All I need is to get this first formatting right first!