0

I'm working on a medicine sheet, where I want to highlight the times when a pill is added to one of the following columns. Right now it looks like this:

sheet

Each pill works for (about) 7 hours, so I take one at 6:30, at 7:30, at 9:30.
I've got everything I wanted in this sheet working how I want, except for this last bit. I want those times in column B highlighted.

I've tried working with INDEX and MATCH, even tried a whole workaround with a second sheet.... I can't figure it out.

EDIT: per request of Rubèn for more info (apologies for not doing so in the first place), I've decided to add a copy of my sheet: meds copy.

Apart from Column A on the meds sheet, basically everything is a result of searching dozens of sites for hours. For finding a way to highlight only one cell when there's more cells with data in them, then finding a way to get the position of the first cell with data in a column and so on and so on until I got where I am now.

Last night I managed to get it almost the way I want it, but as it is now, I have to apply conditional formatting cell by cell.

on the second sheet I check for the first cell with data in a column, resulting in a true/false. I then have that 'converted' to text in column A. on the first sheet I go cell by cell in column B to check if the corresponding cell on the second sheet equals "Found". If yes, it highlights.

I just can't imagine there isn't an easier way to do it.

Thanks in advance for any help :)

New contributor
Melisande Buitenkamp is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1
  • Welcome to Web Applications. Please show the formulas that you tried and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Nov 21 at 18:08
0

For the range B2:B, use this conditional formatting custom formula rule:

=arrayformula( sum( isblank(C1:Z1) * not(isblank(C2:Z2)) ) )

...or, more concisely:

=sumproduct( isblank(C1:Z1); not(isblank(C2:Z2)) )

These formulas work stand-alone and do not require a helper sheet. See your sample spreadsheet.

1

Your Answer

Melisande Buitenkamp is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.