Displaying data by month when there are date gaps in data collected

[BvDA]

Hi,

Raw data collected is at site level. Checks are made and an overall rating given. Carried out approx. once every 3 months per site. (Raw data table on attachment)

At a monthly level the data is reported on to show what rating the site is currently operated at. This means that if the reporting month did not have a rating issued for a particular site that month, the previous closest rating is carried forward as the data. (Summary 1 on attachment gives the expected results)

The data is also presented to the team carrying out the checks in a visual way whereby every check is shown on a row for each site. The date is given, with the cell background colour representing the Red/ Amber/ Green rating given. This data display represents exactly the checks carried out, no infill of months between checks to be shown. (Summary 2 on attachment gives the expected results).

Other notes:
Summary 1 would be in a separate workbook to the other two pieces. The Raw data would be on it's own tab and Summary 2 in another tab in the same workbook.

The grey coloured cells on the sheet are where I plan/ expect to have fixed data references set up to help pull the data together, although this is flexible in order to get a working solution.

I am ok with VB basic code and appending to buttons on sheets so happy to use that where necessary.

Thanks

[63falcondude]

Based on the sample from post #1, try this:

F3 =IFERROR(INDEX($C$3:$C$15,INDEX(MATCH($E3&F$2,$A$3:$A$15&$B$3:$B$15,0),0)),E3)

P3 =IFERROR(INDEX($B:$B,SMALL(IF($A$3:$A$15=$O3,ROW($A$3:$A$15)),COLUMNS($A:A))),"") Ctrl Shift Enter


Now for the conditional formatting.

Highlight P3:S6 > Conditional Formatting > New Rule > Use a formula
=INDEX($C$3:$C$15,MATCH($O3&P3,$A$3:$A$15&$B$3:$B$15,0))="A"
Format: Fill Amber > OK > OK

Highlight P3:S6 > Conditional Formatting > New Rule > Use a formula
=INDEX($C$3:$C$15,MATCH($O3&P3,$A$3:$A$15&$B$3:$B$15,0))="G"
Format: Fill Green > OK > OK

Highlight P3:S6 > Conditional Formatting > New Rule > Use a formula
=INDEX($C$3:$C$15,MATCH($O3&P3,$A$3:$A$15&$B$3:$B$15,0))="R"
Format: Fill Red > OK > OK

See attachment to see all of this being implemented.

[BvDA]

Excellent, thanks. I will give this a go with some real data and update when tested.

[63falcondude]

You're welcome. Let us know how it works out for you.

[BvDA]

Just to let you know, the solution worked perfectly, thank you. Except the machines here are not powerful enough to run the formulae so I will have to look for a vba solution that refreshes on button click I think. Will repost under that section.

Appreciate your help.

[63falcondude]

You're welcome, happy to help.