Forum Discussion
If a "x" value is present and the column header is today's date how do we display true/false
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
- dscheikeyBronze Contributor
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?
- Nyssa1585Copper Contributor
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!
- dscheikeyBronze Contributor