Conditional Formating Question

[BigRed]

 

[NARAYANK991]

Hi ,

Please upload your Excel workbook with the data in it.

Also explain the following :

1. What is the logic for identifying the number 4 ?

Is there similar logic to identify other numbers , or do you wish to restrict the CF only to the number 4 ?

2. Is the highlight to be done only for one row or for where ever the number 4 occurs ?

Uploading a workbook and a complete explanation will get you an answer in the shortest possible time.

Narayan

 

[Eloise T]

View attachment 44216

Are we to understand if for example, Conditional Formatting sees a "4" in cell B2 that you want to highlight cell B2 and populate the next nine columns with highlighted 4s?

 

[BigRed]

Yes that is it, I am trying to develop a planning tool for aircraft maintenance and Check 4's would take a number of days to complete.

 

[BigRed]

I am just using VLOOKUP to search my data to match checks with date. I have managed to get that ok but would like to show that the check 4's actually go for a number of days.

 

[Eloise T]

I am just using VLOOKUP to search my data to match checks with date. I have managed to get that ok but would like to show that the check 4's actually go for a number of days.

I know very little of aircraft maintenance schedules and just enough about Excel to be dangerous; however, you may need to insert a "working" column/cell/row so the formula can pull the data into your open/blank columns (since Excel pulls data into cells rather than pushing it.) I see that NARAYANK991 is watching this thread and he almost assuredly will have a solution once he understands your need...and it doesn't hurt that he's an Excel ninja.

 

[BigRed]

Thanks for your help NARAYANK991 might be able to come up with something. Tried uploading file but can get it to attach, see if the ninja can talk me through.

 

[Eloise T]

Thanks for your help NARAYANK991 might be able to come up with something. Tried uploading file but can get it to attach, see if the ninja can talk me through.

 

[BigRed]

I have just created an example of what my document looks like. The check 4 inspections are what I am trying to repeat so they block out a number of days in the calendar.

 

[r2c2]

@BigRed

I am sure @NARAYANK991 will eventually respond. But here is another solution.

First, add an extra column to your original data and calculate the end date for the inspections (this would be +9 days for Check 4 inspections and same day for others)

Then, assign below names to various ranges

• assets =data!$A$2:$A$10
• end.dates =data!$F$2:$F$10
• start.dates =data!$E$2:$E$10
• inspection.types =data!$D$2:$D$10

Then set up an output sheet like below.




Now in the entire range from B2 to end, just enter a dummy value like 0.
Next, set cell formatting to using custom format code ;;;
This will hide all the zeros. We will now show few of them selectively as "4" thru conditional formatting.

Next, apply below formula based CF rule.

=COUNTIFS(assets,$A2,start.dates,"<="&B$1,end.dates,">="&B$1, inspection.types,"Check 4 Inspection")>0

Apply some fill color and number formatting custom code [=0]"4"

Add additional rules to show other inspections if you want.
Done.

See attached workbook.