+ Reply to Thread
Results 1 to 9 of 9

Weekly granular summary of database

  1. #1
    Registered User
    Join Date
    08-01-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    21

    Weekly granular summary of database

    Hi,

    I’m working on calculating the difference of values in the Database tab Column F “Completion”.
    To determine what two values to calculate it needs to cross-reference and find identical values in Database Column B “Barcode” AND Column K “Time” AND Column N “Tasks” and possibly Column O & P “Check-in/out” if needed to simplify the cross-reference.
    These values should then be summed up and populated in the “Weekly Summary” tab table according to Zones, Date, and Tasks.
    If there is one out of two missing = color weekly schedule cell red.
    Choosing/writing a date and then that week’s summary populates in the table would be the best but I have no idea where to start on that.

    I have reached out for something similar before but after some time the table becomes invalid, and it takes a toll on the sheets size and reaction time.
    I have to admit that I am way out of my depth on this and I really hope someone can come and say it's easy.

    Any suggestions on how I should approach this would be great. Formulas or VBA.
    Attached is the sample workbook with the existing, none working, weekly schedule formula.

    All personal names have been made by a name generator.

    I hope this is clear and that I'm on the right track.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Weekly granular summary of database

    Please add a sample of manually calculated results to the "Weekly Summary".

    I am not sure what you mean by ....

    calculating the difference of values in the Database tab Column F “Completion”.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Weekly granular summary of database

    I also note dates in "Weekly Summary" are June but dates in DB are July/August.

    As per earlier request, can you please add some expected results for at least one date (july/August)

  4. #4
    Registered User
    Join Date
    08-01-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Weekly granular summary of database

    Thanks for your question!

    I've edited and colour coded (sorry) some examples with the calculation of the difference in value that I mentioned.
    I've also attempted to clarify the process and how it is inputted into the Weekly Table.

    I've also hidden some columns and rows to reduce the "noise" of the sheet.

    I hope this clarifies some!
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Weekly granular summary of database

    Thank you for the new file: I now understand your requirement.

    I don't have 365 and so don't have its functionality such as FILTER/SORTBY etc which will probably be key to a formula solution.

  6. #6
    Registered User
    Join Date
    08-01-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Weekly granular summary of database

    Thank you, glad it can be understood!

    I was not aware of the SORTBY function. I've briefly looked at it and that may help. I'll try it out.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Weekly granular summary of database

    VBA:
    Please Login or Register  to view this content.
    Use tabs "DatabaseX" and "Weekly SummaryX"

    "DatabaseX" has extra column "Date" and is sorted by Date/Zone?task/Barcode

    VBA code for sort is in Module 2
    Attached Files Attached Files
    Last edited by JohnTopley; 08-25-2021 at 04:06 AM.

  8. #8
    Registered User
    Join Date
    08-01-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Weekly granular summary of database

    I am so incredibly grateful for this. It works perfectly on the first go! I've implemented it and test ran it several times in all different scenarios. I think my biggest challenge will be to ensure that we get accurate data, with no double entries, "no match" or "missing task".

    You may or may not think much of it but this will help us in a tremendous way, where we can show productivity to customers and sponsors so they see that people with disabilities can work productively.
    And by proving productivity we can get funding to train more people so they can get into mainstream employment and live an open and independent life.
    There are, of course, more things to this but this has been the bottleneck for expansion for years.

    This means a lot for me and the guys.

    So thanks again for taking the time to do this!

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Weekly granular summary of database

    You are very welcome.

    And thank you for providing the background tor this. I know how important it is that those with disabilities are given every opportunity to lead as normal a life as possible. In fact, I watched a TV program on such a subject last night!.

    Keep up the good work.

    A bit of housekeeping please ...

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Last edited by JohnTopley; 08-26-2021 at 01:03 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. weekly summary
    By tauceph in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-05-2019, 02:28 PM
  2. [SOLVED] Percentage from each day into a weekly summary
    By GHI1968 in forum Excel General
    Replies: 3
    Last Post: 07-10-2018, 11:37 PM
  3. [SOLVED] Look up and sum daily data to weekly summary
    By Spamanda in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-11-2016, 01:27 PM
  4. [SOLVED] Weekly summary from daily OHLC data
    By macaonghus in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-08-2015, 03:59 PM
  5. Need to add up data for weekly summary. SUMPRODUCT
    By ARN in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2013, 04:29 PM
  6. Excel 2007 : Help with monthly weekly summary
    By kakashi07 in forum Excel General
    Replies: 2
    Last Post: 03-14-2012, 10:54 AM
  7. [SOLVED] Display a weekly summary
    By markag in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-31-2006, 04:28 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1