0

I have a list of various clients of which each client has some of the 4 products we provide monthly service for. But each product has a different date on which we have to perform the services for the client. Think about different types of air freshener dispensers. I would like to export all the clients that are on schedule for "TODAY" in a separate column, so I may see which products and which clients we should go to.

I already used the formula to highlight TODAY's date in the various columns. But because TODAY's date can apply to various clients and various products, I cannot synchronize them because I can only sort 1 column at a time in order to see TODAY's date, otherwise, TODAY's date is scattered all over the 4 columns in different cells (which links to the client of that specific row).

Please see attached photo. Since I don't want to keep sorting the 4 different columns every time, is there a way I can insert a formula in a separate column that would refer to all the clients that I have to go to, on "TODAY's" date?

P.S. I have searched for many tutorials but just did not find the solutions for this issue. I could only find videos or sites where they show how to highlight upcoming dates etc, but this would have been easy if it was 1 product.

I would appreciate any help because we have over 80 clients and it's a lot of scrolling to find out which client's products are due.

Many thanks in advance!

Regards, Daphne

excel printscren

2
  • 1
    What version of Excel? Mar 16, 2021 at 18:55
  • Hi Ron, Excel for Microsoft 365
    – Daphne
    Mar 16, 2021 at 19:36

2 Answers 2

0

Conditional formatting applied to the first column and with this formula:

=OR(G3=TODAY(),H3=TODAY(),I3=TODAY(),J3=TODAY())

Should do what you want for the first column

For the client name a similar formula may be used in the rightmost column:

=IF(OR(G3=TODAY(),H3=TODAY(),I3=TODAY(),J3=TODAY()),A3,"")
2
  • Hello @cybernetic.nomad thank you very much for responding. I was wondering however, which formula applies to which first column. Because I tried to create a new rule in Conditional Formatting, but it just turns blank afterwards. The G3, H3, I3 and J3 have no data in my worksheet. So I dragged the formula squares to appear where the data should be (in the 4 columns of the dates) but it onle selects 1 cell in that row) I would like to have the full column of dates selected, not just G3 or H3 for example. In my case it's J6, K6, L6, M6 with M6 being jan-5. What am i doing wrong? thanks
    – Daphne
    Mar 16, 2021 at 19:40
  • You don't include column headers in your screen cap. I assumed the first column was column A, counting. Starting form that, columns G, H, I and J are the columns for the Monthly Periodic Service Mar 16, 2021 at 19:59
0

Assuming the date of interest is in K3 (or you could just enter =TODAY() in that cell. And the list of clients will also be in Column K

nn is the last row number in the data. It could be determined dynamically or you could use Tables if your data array will be frequently changing.

Conditional Formatting Column A:

=ISNUMBER(MATCH($A4,$K:$K,0))
Applies to `$A4:$Ann`

Conditional Formatting for the products

=E4 = $K$3
Applies to $E$4:$Hnn

Formula for client list

K4:  =UNIQUE(INDEX($A:$A,AGGREGATE(15,6,1/(K3=products)*ROW(A4:A16),SEQUENCE(COUNT(1/(K3=E4:H16))))))

and the Client list will spill down as far as needed.

enter image description here

4
  • @Ron.Rosenfeld Thank you so much for this detailed explanation! The second formula worked, it highlighted the dates for Jan 15th. I think however I might be doing something wrong with the 1st and the 3rd formula. They do not highlight the data. Also the 'Ann' and 'Hnn' do not appear in the 'Applies to', Excel removes it right after I select APPLY. fyi, I arranged the collumns to be in the exact order as the printscreen your shared, it's column A and K, that I still can't manage to format successfully. May a share a printscreen for you to see or do you already know what might be the issue?
    – Daphne
    Mar 17, 2021 at 20:24
  • @Daphne I have no idea what the issue might be. And I don't know what you mean by formatting with regard to column K, which is where your results are. So if you can clarify what you have done, perhaps I can tell the source of the problem. Mar 17, 2021 at 21:42
  • @Ron.Rosenfeld Hello, the column K (the grey one), has a reference to column A (where the client numbers are ranged from 1 - 13). The formula you gave me >> =ISNUMBER(MATCH($A4,$K:$K,0)) does not apply to $A4:$Ann. When I insert APPLY TO $A4:$Ann, it changes automatically back to $A:$4 and it keeps changing back everytime I want to type in the formula you gave me which is >> $A4:$Ann. The same goes for =UNIQUE(INDEX($A:$A,AGGREGATE(15,6,1/(K3=products)*ROW(A4:A16),SEQUENCE(COUNT(1/(K3=E4:H16)))))). Once I insert this formula it copies succesfully but it does not come into effect with column A.
    – Daphne
    Mar 30, 2021 at 15:13
  • @Daphne Hard to know what you are doing wrong. Are you copying the UNIQUE formula? I didn't write anything about copying it anyplace. With regard to the conditional formatting formula, try selecting the range A4:Ann, with A4 being the active cell. Then select Conditional formatting and enter the formula and the format. Mar 30, 2021 at 15:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .