Search an array located by referencing a date match

[240racr]

Hello all; new to the board.

I'm really stuck on a problem. I'm trying to write a formula that will be used in conditional formatting. I have a calendar that uses macros and self populates the days when selecting a year using a spin button. I want to conditional format each day to change color based on the word 'down' located on another sheet. The other sheet has 10 years worth of dates and time slots; so it's a rather large sheet. I need to find the date (on the data sheet) that matches the date on the calendar, then based on that location it would search a predetermined area for 'down' based on the location of the found matching date. Yeah, probably not the best explanation...

Here is what I came up with (which obviously doesn't work) by just looking up how to use different functions.
=COUNTIF(OFFSET(MATCH(B11,TimeStamp!A1:A6955,0),2,1,17,3),"*down*")

• B11 is the cell that contains self populated date
  
• TimeStamp!A1:A6955 is column that contains dates & times for the first year. The next year starts on F1.

Any help is greatly appreciated.

 

[240racr]

Bump

 

[FormR]

• B11 is the cell that contains self populated date
  
• TimeStamp!A1:A6955 is column that contains dates & times for the first year. The next year starts on F1.

Hi, welcome to the forum!

What is in B11 - is it just the date or a date and time? Where is the text "Down"?. It might help if you post some example data.

 

[240racr]

Hi, welcome to the forum!

What is in B11 - is it just the date or a date and time? Where is the text "Down"?. It might help if you post some example data.

Thank you for the reply FormR. I'l try...

In my example, B11 is just the date and contains =IF(AND(YEAR(Jan+15)=CalendarYear,MONTH(Jan+15)=1),Jan+15,"")
Where CalendarYear is ='2016'!$B$5 (B5 contains the year)
Jan is =DATE(CalendarYear,1,1)-WEEKDAY(DATE(CalendarYear,1,1))

I'm trying to search the following array within conditional formatting. My attempt is with =COUNTIF(OFFSET(MATCH(B11,TimeStamp!A1:A6955,0),2,1,17,3),"*down*")

Down would be input by a user on the sheet TimeStamp which looks a little like this. This array repeats for every day for 10yrs.

1/10/2016	ACE	600	200
	Product	Product	Product
7:00
8:00
9:00
10:00
11:00		Down
12:00		Down
13:00		Down
14:00
15:00
16:00
17:00
18:00
19:00
20:00
21:00
22:00
23:00

<tbody>
</tbody>

 

[FormR]

Hi, give this addition a try:

=COUNTIF(OFFSET(INDEX(TimeStamp!A1:A6955,MATCH(B11,TimeStamp!A1:A6955,0)),2,1,17,3),"*down*")

 

[240racr]

Hi, give this addition a try:

=COUNTIF(OFFSET(INDEX(TimeStamp!A1:A6955,MATCH(B11,TimeStamp!A1:A6955,0)),2,1,17,3),"*down*")

That worked great!!!! Until I expanded to the full array.
=COUNTIF(OFFSET(INDEX(TimeStamp!A1:AW6955,MATCH(B11,TimeStamp!A1:AW6955,0)),2,1,17,3),"*down*")

 

[FormR]

That worked great!!!! Until I expanded to the full array.

Hi, it's the OFFSET() that you need to expand, not the INDEX() or MATCH() ranges, so probably..

=COUNTIF(OFFSET(INDEX(TimeStamp!A1:A6955,MATCH(B11,TimeStamp!A1:A6955,0)),2,1,17,48),"*down*")

 

[240racr]

Hi, it's the OFFSET() that you need to expand, not the INDEX() or MATCH() ranges, so probably..

=COUNTIF(OFFSET(INDEX(TimeStamp!A1:A6955,MATCH(B11,TimeStamp!A1:A6955,0)),2,1,17,48),"*down*")

Hello, that didn't work.

I think the OFFSET may need to be the same. I'll try to explain better in the example below. I think I still need the OFFSET at 17,3 since that is the size of the individual offset search areas.


If the date I'm trying to match is 1/10/2016, then I want it to search for 'down' in the array. (Asterisk's are for formatting color)
If the date I'm trying to match is 1/10/2017, then I want it to search for 'down' in the array. (Asterisk's are for formatting color)
If the date I'm trying to match is 1/10/2018, then I want it to search for 'down' in the array. (Asterisk's are for formatting color)

• 
  
  
  
  
  ​

 

[FormR]

Ah, ok - so the tables go right, not downwards. In that case you need the INDEX() and MATCH() to look at row 2.

=COUNTIF(OFFSET(INDEX(TimeStamp!$A$2:$AW$2,MATCH(B11,TimeStamp!$A$2:$AW$2,0)),2,1,17,3),"*down*")

 

[240racr]

Ah, ok - so the tables go right, not downwards. In that case you need the INDEX() and MATCH() to look at row 2.

=COUNTIF(OFFSET(INDEX(TimeStamp!$A$2:$AW$2,MATCH(B11,TimeStamp!$A$2:$AW$2,0)),2,1,17,3),"*down*")

I want to thank you again for your help.

The tables go across and down. Years go to the right and days go down.

ie. Dates are in the following cells and continue through 2025 and 365 days/year (plus leap year days)

	A	F	K	P....
2	1/1/2016	1/1/2017	1/1/2018	1/1/2019...
21	1/2/2016	1/2/2017	1/2/2018	1/2/2019
40	1/3/2016	1/3/2017	1/3/2018	1/3/2019
59...	1/4/2016...	1/4/2017...	1/4/2018...	1/4/2019...

<tbody>
</tbody>

I have tried the following without luck when it comes to expanding on your first suggestion (which worked when applied only to 2016)

=COUNTIF(OFFSET(INDEX(TimeStamp!$A$2:$AW$2,MATCH(B11,TimeStamp!$A$2:$AW$2,0)),2,1,17,3),"*down*")
=COUNTIF(OFFSET(INDEX(TimeStamp!$A$2:$AW$6955,MATCH(B11,TimeStamp!$A$2:$AW$6955,0)),2,1,17,3),"*down*")
=COUNTIF(OFFSET(INDEX(TimeStamp!$A$1:$AW$1,MATCH(B11,TimeStamp!$A$1:$AW$1,0)),2,1,17,3),"*down*")
=COUNTIF(OFFSET(INDEX(TimeStamp!$A$1:$AW$2,MATCH(B11,TimeStamp!$A$1:$AW$6955,0)),2,1,17,3),"*down*")

Thanks.