Reflecting incomplete data outside a date range

[jnswbc]

I need some input on how to reflect data on column J of the attached SS, while knowing the info below. Is this possible?

If Column J is to identify any reports with a monthly volume, any reports we are out of SLA (based on the Frequency column B), or forgot to work etc. then…

I want to see a Number in column J.

Reports are received at different times of the month, but need to be completed based on the frequency: (D) – daily, (W) – weekly, (M) – monthly.

The volume will be reflected in C:J as inputted onto the Completed Work tab however it will depend on the date. The date the report comes in the report will be updated with the full volume to be worked, as the week progresses and volumes are worked down it will continue to be updated to reflect the volume/date being worked. If not completed by the 7th day the volumes will show in column J until complete.

Only volumes for the reports that reflect out of SLA (column B) will be seen in column J. So if a weekly report was received on 10/30, and it was not completed by Nov 6 (7 days), I should see a volume the for the incomplete work.

SLA is your frequency, column B.

A forgotten report will be IDed with Reference to the frequency column. We will always get a report based on the frequency, although some may show a ‘0’ value, which the ‘0’ would be entered. This is based on frequency; there should always be a value present & if not, we did not pull in and assign the report.


TESTER.xlsx

[newdoverman]

Where is the raw data that you want for column J and what are the criteria for extracting that data?

You bring up the term SLA, what is that?

[jnswbc]

Raw data for column J will come from the INCOMP column(s) on the Completed Work tab.

However, I only want to see an incomplete volume here only if the report listed in column A is not completed within its frequency/time frame/period listed in column B (Daily, Weekly, or Monthly). It will also have to accumulate if more than one period (ie frequency/time frame) passes with incomplete work still available.

So for example, I received the Ocwen – Flood Above report (line 8) on 10/29 with a volume of 25000. It was not completed by 11/5, (the 7th day of the week - the frequency) leaving an incomplete volume of 5000. 5000 (incomplete volume) should be reflected in column J. And as the volume is worked, the value should decrease.

If I receive the Daily Impairments report (line 3) on 10/29 with a volume of 25000 and 5000 were left incomplete on 10/30, 5000 should reflect in column J.
But if 10/29 came in with 25000, 5000 incomplete... & 10/30 came in with 20000, 5000 incomplete, I should see 10000 in column J when I open the spreadsheet on 10/31.

SLA is "Service Level Agreement", or in this case, Column B on the WOH tab titled "Frequency".

All data on the Completed Work tab will be entered manually, where as data on the WOH tab is pulled by formulas thank to y'alls help!

[newdoverman]

I think that you need to reorganize the Completed worksheet to be a vertical arrangement with the dates going vertically down the worksheet along with the various categories. Across the top can be COMP RECVD INCOMP Balance INCOMP with the Balance Incomp column being summed with Sumif so that each category has the correct balance.

I would suggest that the INCOMP amounts be added to the RECVD amounts so that the COMP can in include the INCOMP until there are none.

I have set up two new worksheets with your data and I think that you can see on Sheet1 that you can add a column to recalculate the INCOMP amounts to be part of the RECVD amounts for the next day for each category. This is column H of Sheet1.

Sheet2 is derived directly from Sheet1 and not a copy of the other worksheets so you can see that it works. If you use the filters on the table on Sheet1 you can isolate each category to see what is going on with them.

[jnswbc]

Thank you newdoverman for all your input and hard work on the new worksheet. I think I had a moment of clarity last night thinking about how to explain what I am looking for better. I just have one other question...

If a value is entered in any of the INCOMP columns on the Completed Worksheet tab, more than 30 days (or 7 days, or 1 day) ago based on Today's date [WOH!C2=TODAY()], can it be reflected in column J as a sum of those values...? For example, if I am concerned with a 7 day time frame... Today is 11/11. If any data is in the INCOMP columns 11/4 or earlier, I need the sum of those cells in the WOH tab, in column J.

[newdoverman]

You can get the totals for INCOMP for dates preceding the 7 days shown on Sheet2 for each category in column B of Sheet2 with this formula entered in J3 and filled down.

Please Login or Register  to view this content.

The vertical arrangement on Sheet1 in a table (the table amends automatically all the formulae dependant upon the data in the table) makes for very easy calculation of various totals without getting into the complications associated with a horizontal arrangement. Just remember to copy the categories and paste them down the column and enter the appropriate date beside them and fill down.

[jnswbc]

What is the difference, aside from the formula between column F & G on sheet 1? What is column H and does that affect any other formulas should I delete it?

If I change the order of the tabs, will that affect the formulas inputted in sheets 1 & 2?

[jnswbc]

You can get the totals for INCOMP for dates preceding the 7 days shown on Sheet2 for each category in column B of Sheet2 with this formula entered in J3 and filled down.

Please Login or Register  to view this content.

The vertical arrangement on Sheet1 in a table (the table amends automatically all the formulae dependant upon the data in the table) makes for very easy calculation of various totals without getting into the complications associated with a horizontal arrangement. Just remember to copy the categories and paste them down the column and enter the appropriate date beside them and fill down.

Is there a way I can do this with the worksheet I provided? I inputted a day/date above the INCOMP field name (like I did over the RECVD column), and have updated the formulas down the INCOMP columns so that a number will only display if it is greater than zero. I don't understand Excel language, so I am having difficulty writing a formula that gives me a sum of values from the INCOMP columns if they exist more than thirty days from todays date.

I'm thinking a SUMIF formula is needed...?

[newdoverman]

Your latest upload has calculations that are incorrect. I have corrected them in this workbook and have added columns to get the stats that you want on Sheet1.

[jnswbc]

Please advise which calculations are incorrect? I'm having difficulty understanding what is going on...

Also, what is the difference, aside from the formula between column F & G on sheet 1? What is column H and does that affect any other formulas should I delete it?

If I change the order of the tabs, will that affect the formulas inputted in sheets 1 & 2?

[newdoverman]

When you changed the columns being retrieved, the calculations were retrieving the wrong columns for the WOH worksheet. I changed the formula to using INDEX and MATCH to address the correct values. The workbook that I uploaded, has the corrections made for you.

The HLOOKUP isn't flexible enough to adapt to the changes you made unless you were very careful with the dates because the dates were the lookup value and the first values are retrieved which are not necessarily the ones that you want. The Index and match formula that I gave you matches both the date and the column header so you get the correct value.

[jnswbc]

what is the difference, aside from the formula between column F & G on sheet 1? What is column H and does that affect any other formulas should I delete it?

If I change the order of the tabs, will that affect the formulas inputted in sheets 1 & 2?

[newdoverman]

I think that I may have come up with something useful for you.

I added a counter in Sheet1 that will increment the day once you have filled in the previous day (26 rows). This leaves you with filling in the day of the week because my DAY function is giving the wrong day in your workbook.

I have changed the formulae to use Table nomenclature to be absolutely sure that the ranges in the formulae are correctly updated with new rows. It seems that when adding batches of rows, the formulae were not updating properly. This should fix that. I also added daily averages on Sheet2 for COMP and RECVD as well as a total for column J.

[jnswbc]

I added a counter in Sheet1 that will increment the day once you have filled in the previous day (26 rows). This leaves you with filling in the day of the week because my DAY function is giving the wrong day in your workbook.

Do you mean I just have to manually enter the day down column B? Is column A hidden because it's a helper?

[newdoverman]

Yes, manually enter the name of the day in column B. Column A (hidden) is just a helper column giving a method to advance the date after 26 entries.

[jnswbc]

How did you use the name manager to create the Table 1 name range for the formula you inputted into Column L? I am trying to replicate it, but I can't seem to figure it out. That particular formula is the only one I've come across/tested that actually gives me a true rolling two week average value.

[newdoverman]

The table definition isn't changed in the Name Manager. Click in the table, Click on the Design Tab, click on Resize Table then amend the table definition.

[jnswbc]

Another question, and please, let me know if I am out of line...!

For Sheet 2, Column J, I notice the formula is based on 7 days, or rather cell I2. If I need to adjust the formula for 1 day and 30 days, can I reference those values in a another column/cell and hide them or can I actually input the values of 1 and/or 30 as needed?

[newdoverman]

I2 is used to determine the end date (latest date prior to the present week).
I amended the formula to use Table nomenclature. This is for -1 Day

Please Login or Register  to view this content.

This is for -30 Days

Please Login or Register  to view this content.

This workbook has the amended formula for column J and the two new formulae.

[jnswbc]

Correct me if I'm wrong, the coding above will provide the sum of values under incomp on sheet 1 if present more than 1 day ago and thirty days ago based on todays date?

[newdoverman]

The formula for 1 day gives the value for 1 day prior to the date in I2 for each category the second formula gives the total for 30 days prior to the date in I2 for each category.

[newdoverman]

If you want the total up to the day before the date in I2 this will do it

Please Login or Register  to view this content.

For the total to end 30 days prior to I2

Please Login or Register  to view this content.

[jnswbc]

I am looking for the total in 3 different instances...

If there is values in the incomp columns more than 1 day from today, 7 days from today, and 30 days from today.

So the formula provided on the workbook referring to I2 would work regarding 7 days.
I was thinking I could simply replace I2 with D2 to make it calculate for a 1 day ago and in similar fashion for the 30 day.


Does that make sense? I don't know the Excel lingo so...

[newdoverman]

You can do that. If you are filling the time down make sure that you use the EXACT cell referencing method as used for I$2.

[jnswbc]

Perfect, thank you SO SO much for your assistance!!!!!!

[newdoverman]

You're welcome. Thank you for the feedback.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.