0

I have a sheet named Equipment with two columns G "Purchased Date" and H "Installation Date". A separate sheet named December is a calendar that is populated with data from Equipment when the dates match.

In the example below, I3 in December is 12/01/2020

=ARRAYFORMULA(IFERROR(INDEX(Equipment!A2:D,SMALL(IF(I3=Equipment!G2:H,ROW(Equipment!G2:H)-1),1))))

I would like to indicate in December that the data is a "Purchase Date" or an "Installation Date", preferably through conditional formatting (One is red font the other is blue font). The only solution I can think of is to force user to have two separate rows, one for "Purchase Date" and one for "Installation Date". Is it possible to avoid doing this?

Equipment

December

Zed
  • 19
  • 1
  • 5
  • 1
    Zed, to make it easier for us to help you, please share a sample copy of your sheet(s) - see this guide: https://support.google.com/docs/thread/3808684?hl=en. But yes, this should be possible to do. – kirkg13 Dec 15 '20 at 18:02
  • Thank you: https://docs.google.com/spreadsheets/d/1hpYSnDRZ1_2MFMCzxglzqSktH8G0pcyXWasdpDw0CT4/edit?usp=sharing – Zed Dec 16 '20 at 17:00

1 Answers1

1

I came up with an answer that you may want to try. But it does involve a helper column for each day of the week. See my sample sheet here. I've used columns AS to AW on the far right for this. In AS, I put this formula:

=ArrayFormula(COUNTIFS(Equipment!$A$2:$A,D5:D,Equipment!$G$2:$G,$D$3))

This does a COUNTIFS in the Equipment tab for records that have the same Project Name as this row, and a purchase date equal to the date for this day of the calendar. This returns a 1 or a 0.

Then conditional formating rules are used to change the Project Name (in column D) to red text if column AS=1 (a purchase date), or blue text if not. If you predefined all of the text in this column as red (or blue) you would only need one CF rule for the whole column, making this easier to set up. You already have CF rules for your date headers, so they could override the default font colour (red or blue) assigned to the column, without needing an additional rule.

Note also that I found an alternative to your formula in D5 (and I5, N5...):

=iferror(query(Equipment!$A$2:$I,"Select A,B,C,D 
    where (G = date '"& text(D3,"yyyy-mm-dd") & "' 
        or I = date '"& text(D3,"yyyy-mm-dd") & "') order by A",0))

This returns all of the results for this day, with one formula.

enter image description here

halfer
  • 19,824
  • 17
  • 99
  • 186
kirkg13
  • 2,955
  • 1
  • 8
  • 12
  • Thank you! I learned so much from this. I wasn't aware of the query function. Even if I was, it probably wouldn't have occurred to me to use it. Slick solution for conditional formatting as well. I really appreciate it. – Zed Dec 17 '20 at 20:24
  • Glad it helped. It may be a little tricky to get it to build the hyperlinks to a variety of tabs, but definetly possible. The following (similar) question may be of help, or ask a new question with a bigger sample of demo tabs, to get a more precise formula to exactly meet your requirement. https://stackoverflow.com/questions/48126574/how-to-insert-hyperlink-to-a-cell-in-google-sheet-using-formula – kirkg13 Dec 17 '20 at 20:53