+ Reply to Thread
Results 1 to 13 of 13

Multiple Dynamic rows to be filter based criteria on another sheet

  1. #1
    Registered User
    Join Date
    01-02-2022
    Location
    India
    MS-Off Ver
    2016
    Posts
    7

    Multiple Dynamic rows to be filter based criteria on another sheet

    Hi All, Good Day ..

    I just started learn more about excel deep nowadays, I got promotion in office.
    I was assigned to small task in excel, which a am suck now, can anyone just lift me up from this issue.

    I have some multiple sheets with dynamic rows to be updated daily
    I have 3 sheets with 3 teams data on it and one main sheet(summary) for consolidate, filter all data from other 3 sheets

    Sheets data have "date"(date), "name"(text), "site"(text), "shift" (text),"project" (text),"task" (text),"complexity" (text),"hours worked" (number), "notes"(text), all 3 sheets contain these data filled up, and it dynamic(every day it will be updated with new data below it)
    The main summary sheet have "From"(date), "To"(date) "name"(text)(drop-down list)


    concern:
    In summary sheet, when I enter From date and To date, it should filter and do drop-down list all the names in that given dates from all three sheets and remove duplicate, then show the respective results(person) in drop down list in cell B4, when i select that person from list and it should show result in ("task count"(E2), "hours worked"(E3), "leave count"(E4)) and their respective of specific data("date"(date), "name"(text), "site"(text),"project" (text),"task" (text),"hours worked" (number), "notes"(text)) show below.

    In summary sheet
    Name-B4 Cell: based on two dates, it should load all the names from 3 sheet for those dates and remove any duplicate, and load it in data validation drop-down list
    Task Count-E2 Cell: It should count all task done by that person on those specific given dates, some time same person will do two task on same date, so it should count 2 task on that day, should check in all 3 sheets
    Hours Worked-E3 Cell: It should sum all total hours worked by that person on those given between two dates, and show the result in number, should check that name in all 3 sheets for that person
    Leave-E4 Cell: It should count total number of leaves took by that person on those given between two dates, should check that name in all 3 sheets for that person

    When I select the person(drop-down list) from B4, it should show the respective data in "E2""E3""E4" cells

    Show Results of that person(drop-down list) selected, which those filtered from above, the cells(A8:G8)(DATE, NAME, SITE, PROJECT, TASK, HOURS WORKED, NOTES)

    I am using old version of basic excel 2010, and some time might use online excel 365, (I won't able access for VBA and macros)
    so, its better to help me out using only formula will be better.

    Thank you all for reading the post and helping me out .
    Attached Files Attached Files
    Last edited by naren_go; 01-04-2022 at 08:19 AM.

  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
    27,999

    Re: Multiple Dynamic rows to be filter based criteria on another sheet

    Please see yellow banner at top the page on how to attach a sample workbook. Be sure to include expected results.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Registered User
    Join Date
    01-02-2022
    Location
    India
    MS-Off Ver
    2016
    Posts
    7

    Re: Multiple Dynamic rows to be filter based criteria on another sheet

    Sorry John, I didn't saw that, I just updated my post ..

  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
    27,999

    Re: Multiple Dynamic rows to be filter based criteria on another sheet

    I used Power Query (of which I am a complete novice!) to produce a single table (Sheet4)

    See .. trumpexcel.com/combine-multiple-worksheets/

    I then extracted the names, pasted to column K of "Summary" and "Removed Duplicates" to create named range "Names"

    B4 data validation=: =List Refers to: =Names

    in A8

    =IFERROR(INDEX(Query1[Content.DATE],AGGREGATE(15,6,ROW(Query1[[Content.DATE]:[Content.DATE]])-1/(Query1[[Content.DATE]:[Content.DATE]]>=summary!$B$2)/(Query1[[Content.DATE]:[Content.DATE]]<=summary!$B$3)/(Query1[[Content.NAME]:[Content.NAME]]=summary!$B$4),ROWS($1:1))),"")

    and similar for other columns changing highlighted reference

    In E2

    OUNTIF($B$8:$B$100,$B$4)

    in E3

    =SUMIFS($F$8:$F$100,$B$8:$B$100,$B$4)
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-02-2022
    Location
    India
    MS-Off Ver
    2016
    Posts
    7

    Re: Multiple Dynamic rows to be filter based criteria on another sheet

    Hey John, very much appreciate for help me,

    some doubt, in summary sheet.
    - When i add new record data in Sheet1 or Sheet2 or Sheet3, its not updating in summary sheet (when that name and date is filtered properly).
    - I don't want all name load in that B4(name)(list) - names to load only in between that given two dates From date(B2) AND To date(B3) even new record is added in last, without duplicate name.
    - When a person is leave, leave should count on E4 cell (leave count)and should not count as task count value in (E2).

    Thank you so much for your time.

  6. #6
    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
    27,999

    Re: Multiple Dynamic rows to be filter based criteria on another sheet

    Formula for "Leave"

    =COUNTIFS(Query1[Content.NAME],B4,Query1[Content.DATE],">=" &B2,Query1[Content.DATE],"<=" &B3,Query1[Content.PROJECT],"Leave")

    To refresh the data:

    with "Consolidated" tab selected, In "Power Query" click on "Show Pane". Click on "icon" on top right of pane (text "Refresh") and the data will be updated.

    To create the name list defined by the dates I used helper column in column K of "Summary"

    in K2

    =IF(AND(Consolidated!$A2>=summary!$B$2,Consolidated!$A2<=summary!$B$3,COUNTIF(Consolidated!$B$2:$B2,Consolidated!$B2)=1),Consolidated!$B2,"")

    in L2: this is named range "Names"

    =IFERROR(INDEX($K$2:$K$400,AGGREGATE(15,6,ROW($A$1:$A$400)/($K$2:$K$400<>""),ROWS($1:1))),"")
    Attached Files Attached Files
    Last edited by JohnTopley; 01-05-2022 at 12:52 PM.

  7. #7
    Registered User
    Join Date
    01-02-2022
    Location
    India
    MS-Off Ver
    2016
    Posts
    7

    Re: Multiple Dynamic rows to be filter based criteria on another sheet

    Hey John, Good Day ..

    All Good .. last few little things ..
    -when a person is leave in Summary sheet - Task Count(E2) count that leave as a task, can you make that one exception, not to count leave as task
    -Is there any way to refresh the power query automatically, every time a new data entered in 3 sheets
    -In Summary sheet - name (B4) drop-down list, so many empty values are showing, if possible, is there any way to remove it ..

    Rest everything is AWESOME ..

  8. #8
    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
    27,999

    Re: Multiple Dynamic rows to be filter based criteria on another sheet

    Updated W/book.

    As far as I can ascertain there is no automatic way of updating PQ if a sheet is changed.
    Attached Files Attached Files

  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
    27,999

    Re: Multiple Dynamic rows to be filter based criteria on another sheet

    See attached which has button on "Summary" to "Refresh Data" (Thanks to Alan Sidman for advice on how to do this).
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-02-2022
    Location
    India
    MS-Off Ver
    2016
    Posts
    7

    Re: Multiple Dynamic rows to be filter based criteria on another sheet

    Hey John, Good Day ..

    All fine working in offline in local with the file, when i upload this workbook in Online with my office 365 account .. I got some error while refresh the data in "Consolidated" sheet.

    Error:
    "Data Refresh Failed
    We were unable to refresh one or more data connection in this workbook. The following connections failed to refresh: Query - Query1"

    Is there any solution for that ?

  11. #11
    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
    27,999

    Re: Multiple Dynamic rows to be filter based criteria on another sheet

    I don't have 365 so I cannot help with your problem although I suspect it is because it is an "issue" with the Online version,

    And please stick to text in black rather than adding unnecessary text colouring.

  12. #12
    Registered User
    Join Date
    01-02-2022
    Location
    India
    MS-Off Ver
    2016
    Posts
    7

    Re: Multiple Dynamic rows to be filter based criteria on another sheet

    Thanks for the update and help John ..

  13. #13
    Registered User
    Join Date
    01-02-2022
    Location
    India
    MS-Off Ver
    2016
    Posts
    7

    Re: Multiple Dynamic rows to be filter based criteria on another sheet

    Hey John,
    I found the formula for stacking multiple set of ranges from different sheets in one sheet.
    works only in office 365

    found some formula in exceljet website.
    they had only for 2 range, my friend expand the formula to 4 set of ranges


    =LET(range1,Table1,range2,Table2,range3,Table3,range4,Table4,
    rows1,ROWS(range1),
    rows2,ROWS(range2),
    rows3,ROWS(range3),
    rows4,ROWS(range4),
    cols1,COLUMNS(range1),
    rowindex,SEQUENCE(rows1+rows2+rows3+rows4),
    colindex,SEQUENCE(1,cols1),
    IF(rowindex<=rows1,INDEX(range1,rowindex,colindex),
    IF(rowindex<=(rows1+rows2),INDEX(range2,rowindex-rows1,colindex),
    IF(rowindex<=(rows1+rows2+rows3),INDEX(range3,rowindex-(rows1+rows2),colindex),
    INDEX(range4,rowindex-(rows1+rows2+rows3),colindex)))))

+ 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. Replies: 7
    Last Post: 12-10-2021, 02:17 PM
  2. [SOLVED] Filter column based on multiple criteria on another sheet
    By Rabbitoh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-15-2021, 12:45 AM
  3. [SOLVED] Macro to hide rows (Auto filter) based on multiple criteria
    By pauldaddyadams in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-03-2014, 07:36 AM
  4. Filter data from a Master sheet based on multiple criteria by VBA code
    By judeprem in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2013, 03:27 PM
  5. [SOLVED] Filter and export to a new sheet based upon multiple criteria
    By Cody1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-05-2012, 07:03 PM
  6. Filter based on multiple criteria in other sheet
    By prashantsd in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-17-2012, 09:08 AM
  7. Copy rows from one sheet to another based on multiple criteria
    By Abe8192 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-01-2012, 04:33 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