If a "x" value is present and the column header is today's date how do we display true/false

[Nyssa1585]

I have a workbook that I am trying to update to display the status (red or green) in cell AB1 of the lab in a singular cell based on =Today(). The table is a calendar format but due to some other pivot tables and other data validation features they have some separating columns (an example workbook is attached). The basic function of this is to track the daily events in each category/swim lane. An x marks that that issue happened on that particular day. So I am trying to come up with a function that will search the table for the specific date and if there are any x's in the column below to then have a true/false scenario. I was searching through V/XLookup, MATCH, and IF functions but they all don't quite seem to fit.

Anyone have any advice? 

Also to note, secondary issue is that I wasn't able to have the dates as the specific column headers in the tables so I opted above but if there is a solution to that as well, I'd be very grateful. Any formula I try doesn't stick as it needs to be referenced to the year input as this is a rolling template from year to year. 

Thank you so much, 

Nyssa

[#2]

@Nyssa1585 

Question no. 1: Could you please upload the worksheet without data here or to another file hoster so that I don't have to spend a lot of time recreating the scenario for the solution. Thank you.

Question no. 2: Did I understand correctly? If there is an "x" anywhere in today's column, AB1 should be given the cell colour red, otherwise green? Does the status field also exist for the other months or does only this one apply to January for the whole year?

[#3]

@dscheikey 1) Not sure why this page is saying any .xlsm,. .xlsx or any other file type is not supported but hopefully this host site will work instead: https://uploadnow.io/f/zCH1Shq

2) you are exactly right, if no x is present it should be green. The idea would be to have one status bar for the whole year. 

Thank you for your time!

[#4]

@Nyssa1585 

[#5]

Does this work: https://healthservices.sharefile.com/d-sed6466342f08461aaad73854081459a7 or this https://www.dropbox.com/scl/fi/130icpsw0tpiqf4nbwxqw/Lab-Status-Workbook.xlsm?rlkey=0te29lf432w426d0...

[#6 · Accepted solution]

@Nyssa1585 

For conditional formatting:

=OR(INDEX(A3:PN77,0,XMATCH(TODAY(),A3:PN3))="x")

[#7]

That worked!! thank you so much for your help!

[#8]

@dscheikey Follow up question if you have time if I was to put each month on a separate sheet to display that status on the first sheet in L4, how would that array for the Index change in the formula.

like in: https://www.dropbox.com/scl/fi/2r4cue9czdbu56ij7qkmp/MDI-RED_Greenseparate.xlsm?rlkey=0msxuez5nr62mv... 

[#9]

@Nyssa1585 

Please try:

=OR(INDEX(INDIRECT(TEXT(TODAY(),"MMMM")&"!A3:AH77"),0,XMATCH(TODAY(),INDIRECT(TEXT(TODAY(),"MMMM")&"!A3:AH3")))="x")

[#10]

Doesn't seem to work, but thanks for trying.

[#11]

@Nyssa1585 

What result do you get if you enter the following formula in Excel?

=TEXT(TODAY(),"MMMM")

It could be due to the different regional settings.

It works for me!

Please note that you have still set 2022 as the year in your example document.

[#12]

You were absolutely right on the year; syntax user error. Thank you so much!!!