Status Report

Vesta

Hi everyone!  I have a grid that contains a form for a status report, that needs to be filled out twice a month-the exact dates will vary each month.  What I am trying to do is twofold; first, create something that will allow me to see who has/hasn't filled out the status report for that period, and secondly, if possible, display that on a dashboard.  It could also contain something that shows, for each status report period, if they have completed their report.  I've been thinking about how to do this for a few days now, with no luck.  I would love something that looks like the attached, but the example is still just a grid and doesn't get me on the dashboard... any help would be appreciated!

 

Paul Newcome

Are you able to post some screenshots of your source sheet where you are collecting the entries? Sensitive/Confidential data can be hidden, removed, blocked out, or replaced with "dummy data", but being able to see what you CURRENTLY have would be a huge help.

Vesta

Hi Paul - Here's a (dummy) portion of the data I'm working with - the rest of it is additional columns similar to Q1 and are just for gathering data.  Thank you!

Paul Newcome

Ok. That certainly helps. Question time...

.

How are they filling out the report? Manually, via form, other?

.

How do you determine which date period the submitted report is for? Is it the Created date or a manually entered date or even a specific text such as "1st Quarter" or something else?

.

Once we get the questions answered (there may be more), I will spell it out as I understand it and have you confirm. That way we don't waste a bunch of time working on a solution that doesn't fit what you need.

Vesta

You're my hero!  

To your questions:  they fill out the report via a form that feeds into this grid.  The date period for the report is based on the created date.  For example, we asked for the reports to be filled out for August, first by August 9th, then August 29th.  There are always a few that will come in late, of course, but in general it's the first half of the month, then the latter half. 

I'm happy to answer any other questions that may help.  I've spent days trying to wrap my head around this and keep coming up blank.  I appreciate your help!

Paul Newcome

So basically...

 

You want to look at the Name field and see if there is an entry for that name where the Created date column has an entry between two specific dates?

Vesta

Yes, that sounds about right!

Paul Newcome

Ok. This is actually going to be a relatively straightforward build, but...

 

I get to start teaching my daughter how to fish this weekend, so I am ready to get out of here and get home. Haha.

 

We'll get the build started on Monday. 

Vesta

Appreciate it!  Hope you and your daughter have a great weekend 

Paul Newcome

Alright. So here we go... In the first screenshot you will see the Master Sheet. This is the sheet where the actual submissions go. In the second screenshot is the Summary sheet where your metrics are reported.

 

Here's the breakdown:

 

Master: Nothing changes in your setup. I used manually entered dates just so I could plug in different dates for a variety for testing, but the way the formulas are written, you will be able to use them as is for a System Created Date column.

.

Summary: This is where the magic happens. You will see your standard columns and rows where you have your names listed and the series of red or green indicators.

.

You will also see 4 additional rows below the last name as well as an additional column named "Current". The additional rows provide the numbers for the dates to be compared against, and the Current column will pull the data from the column that is displaying the most recent date range. The Current column is what you will use alongside the Name column for your dashboard display.

.

The 4 additional rows have the following data in them. I will refer to the text that is within the Name column as opposed to actual row numbers just to make it easier to understand what is in these rows and why.

.

In the row labeled "Year", enter the year you are wanting to pull these metrics for.

In "Month", enter the month.

"Day (Low End)" is where you put the earliest date for that column's range.

"Day (High End)" is where you put the latest date for that column's range.

.

I suggest filling out these additional rows first as they are the driving factor behind the formulas. Having data in them while populating the actual statuses will give you the ability to make sure they are actually working.

.

From there we will move on to row 1. Skip the Current column for now. Go to the first column after that and enter

 

=IF(TODAY() >= DATE([June 1 - 15]$7, [June 1 - 15]$8, [June 1 - 15]$9), "June 1 - 15")

 

Adjust the row references to point at the rows holding the corresponding year, month, and day numbers for the low end of the date range.

Where it says "June 1 - 15", replace the text within to reflect the appropriate date range for that column.

 

This will leave the cell blank until the date range for that column is the current date range. We will use this same idea with the red/green indicators as well.

. 

You can dragfill this formula across the row and then just manually update the displayed date range for each column.

.

Skipping the Current column again, we will move to the first row and column of the actual red/green indicators. In my example the cell reference would be [June 1 - 15]2.

 

Here we will enter:

 

=IF(TODAY() >= DATE([June 1 - 15]$7, [June 1 - 15]$8, [June 1 - 15]$9), IF(COUNTIFS({Master Name}, $Name@row, {Master Date}, AND(DATEONLY(@cell) >= DATE([June 1 - 15]$7, [June 1 - 15]$8, [June 1 - 15]$9), DATEONLY(@cell) <= DATE([June 1 - 15]$7, [June 1 - 15]$8, [June 1 - 15]$10))) = 0, "Red", "Green"))

.

The date functions will need to be updated with the appropriate cell references to reflect the correct column and rows.

.

NOTE: The row and column references that are locked in using the $. This is intentional. It will allow you to dragfill this formula to populate the rest of the chart.

.

Now we can move to the Current column. This one is pretty simple as we will be using the same formula down the column.

 

=INDEX([June 1 - 15]@row:[Sept 16 - 30]@row, 1, COUNTIFS([June 1 - 15]@row:[Sept 16 - 30]@row, NOT(ISBLANK(@cell))))

 

What this does is it looks across the row and pulls the data from the last non-blank column (this is the reason behind leaving them blank if they are "future" columns).

.

Once you dragfill that formula down the rest of the names, you will have your current date range right next to your names which allows you to display the current data on the dashboard without having to regularly update the chart widget, and everything to the right will be your historical data.

.

Give this a whirl and let me know if you have any questions. 

Vesta

Ahhh this worked beautifully! Thank you so, so much!

Paul Newcome

Happy to help!