+ Reply to Thread
Results 1 to 6 of 6

Complex get multiple values or sums from table for date range based on dropdown selection.

  1. #1
    Registered User
    Join Date
    08-04-2010
    Location
    United States
    MS-Off Ver
    Office 365 for windows
    Posts
    62

    Question Complex get multiple values or sums from table for date range based on dropdown selection.

    Hello. I have a problem with a spreadsheet here that I cannot figure out.

    On the PayrollEntry sheet I've got a table with the following headers:

    Date
    Employee
    Time In
    Time Out
    Advance
    Total Shift Time hh:mm
    Total Shift Time

    On the left side of the table I've got a formula using =OFFSET(Sheet2!A:A,0,0,COUNTIF(Sheet2!$A:$A,">*"),1) to display a unique list of names of employees.

    On the PayrollEntry sheet in cell A3 I have a date range dropdown box.

    What I need is Column D2:D27 to populate with the sum of hours (decimal) worked for the date range in the dropdown box and in column E from E2:E27 to show the sum of any Payouts the employee took in that date range chosen in the dropdown box.

    I have it working with VBA but it's super slow and really awful code. I'm wondering if its possible to do the same thing faster with excel formulas.

    I've left data in that shows how I would like it to populate.

    If possible I would like it to leave out total hours worked and total payouts if there are no employee names present to calculate those values from. Currently it produces zeros even though there are no employee names in column C.

    Also if possible the dropdown list would display Feb_16 - Feb_29 if it is a leap year otherwise display Feb_16 - Feb_28.

    I'm doing my best to help my mom with this so help would be absolutely amazing.
    Attached Files Attached Files
    Last edited by lOYvEpi6M87nEoIF0ul8; 03-18-2021 at 04:16 PM.

  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,000

    Re: Complex get multiple values or sums from table for date range based on dropdown select

    I think Sheet1 is missing.

  3. #3
    Registered User
    Join Date
    08-04-2010
    Location
    United States
    MS-Off Ver
    Office 365 for windows
    Posts
    62

    Re: Complex get multiple values or sums from table for date range based on dropdown select

    Sorry. Sheet one was renamed to PayrollEntry. I updated the main post. Sorry about that ><.
    Last edited by lOYvEpi6M87nEoIF0ul8; 03-18-2021 at 04:17 PM.

  4. #4
    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,000

    Re: Complex get multiple values or sums from table for date range based on dropdown select

    Your Date Drop needs proper dates with Year: i suggest To/From as separate dates (Excel dates)

  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,000

    Re: Complex get multiple values or sums from table for date range based on dropdown select

    in D2

    =SUMIFS($I$29:$I$1000,$D$29:$D$1000,C2,$C$29:$C$1000,">=" &$A$5,$C$29:$C$1000,"<=" &$A$6)

    Dates in A5/A6
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-04-2010
    Location
    United States
    MS-Off Ver
    Office 365 for windows
    Posts
    62

    Re: Complex get multiple values or sums from table for date range based on dropdown select

    Excellent thank you so much. This works great :D I think I can figure out how to adapt it to include the payouts.

+ 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. Multiple Dropdown selection & Column Groupings based on dropdown selection
    By rabrol in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2018, 10:23 AM
  2. [SOLVED] Error when creating pivot table based on user date range selection
    By BG1983 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-10-2016, 04:50 PM
  3. [SOLVED] Use VBA to populate Range based on Dropdown date selection
    By Invicta084 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-09-2015, 08:58 AM
  4. Replies: 3
    Last Post: 12-18-2014, 07:20 AM
  5. Replies: 9
    Last Post: 02-26-2014, 04:17 PM
  6. Replies: 8
    Last Post: 02-10-2013, 01:15 PM
  7. Return Multiple Values Based On Dropdown List Selection
    By tigabalm in forum Excel General
    Replies: 5
    Last Post: 04-01-2010, 07:36 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