+ Reply to Thread
Results 1 to 7 of 7

Dynamic overview table that sorts automatically/refreshes based on changes in values.

  1. #1
    Forum Contributor
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    131

    Dynamic overview table that sorts automatically/refreshes based on changes in values.

    Hi,

    Require a formula help for dynamic overview table that sorts automatically based on total values from all the weeks stated as headers in a table(Quantities) and refreshes instantly always as per entering the values, and change the positions of each product as per their sum of total quantities.

    Finally in an total weeks which product stands for producing largest sum of quantity that would be recorded as in overview table serial number 1 position as like same manner second highest would be second position as like mentioned in expected results.

    In a work sheet cells A1 to k8 that are the cells belongs to Overview table where the results to be reflect and expected results are mentioned there.

    And the data from where results to be reflect in overview table beginning from Cells C10 to J20 as named (Lot-1 and Lot-2).


    Thanks for the help.
    Attached Files Attached Files

  2. #2
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: Dynamic overview table that sorts automatically/refreshes based on changes in values.

    Try C4 cell formula , Drag down and accross

    HTML Code: 

  3. #3
    Forum Contributor
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    131

    Re: Dynamic overview table that sorts automatically/refreshes based on changes in values.

    Thank you WK9128 for your valuable response to my query,


    Any how your provided solution may be not that what exactly I am looking, Once again I am trying to explain the what sort of solution I looking for,

    I attached revised sheet in that values updated till week-6 in both, and data source table named as Lot-1 & Lot-2 (C:10 to J:20), from both tables as per products data sum values are updated in the overview table till week-6.


    And as per product wise updated sum values are positioned largest (Product-Apple) to smallest (Product-Orange) as updated till week-6. Now coming weeks,week-7/8 if any values are exceeded than apple for example grapes get values-200 in the week-7/8 then its total sum values stands (328+200=538),and apple get 20 only in the week-7/8 then its total sum values stands (490+20=510),now the position changes to grapes become as a largest value produced and apple down its position second.

    The above said thing to be updated in overview table automatically with sorting the positions as based on total sum values and refreshes instantly every time as entering values .



    Thanks,
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,429

    Re: Dynamic overview table that sorts automatically/refreshes based on changes in values.

    This proposal inserts a helper column (A11:A20) which may be moved and/or hidden for aesthetic purposes.
    The helper column is populated using: =IF(OR(B11="",C11=""),"",IF(COUNTIFS(C11:C$20,C11)>1,SUM(SUM(D11:Z11),SUMPRODUCT(--(C$17:C$20=C11)*(D$17:Z$20))),IF(COUNTIFS(C$11:C$20,C11)=1,SUM(D11:Z11),0)))
    The names in C4:C8 are populated using: =INDEX(C$11:C$20,MATCH(LARGE(A$11:A$20,B4),A$11:A$20,0))
    The weekly totals are populated using: wk9128's formula.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Contributor
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    131

    Re: Dynamic overview table that sorts automatically/refreshes based on changes in values.

    Thank you so much JeteMC for your nice formula solution.

    here is i have trying out to get the solution from various searching's (link see in attached book), but its covering only Lot-1 table, now remaining i want to add Lot-2 table in the formula, could someone help out and alter the formula or any other specific way to outcome the required results from both table.

    see below

    =IFERROR(INDEX($C$12:$K$15,MATCH(LARGE(MMULT(IF($D$12:$K$15<>"",$D$12:$K$15,0),TRANSPOSE(COLUMN($D$11:$K$11)^0)),ROWS($A$1:B1)),IF((COUNTIF($C$3:D3,$C$12:$C$15)=0),MMULT(IF($D$12:$K$15<>"",$D$12:$K$15,0),TRANSPOSE(COLUMN($D$11:$K$11)^0)),"A"),0),COLUMNS($A$1:B1)),"")


    Thanks,
    Attached Files Attached Files

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Dynamic overview table that sorts automatically/refreshes based on changes in values.

    Maybe try at
    C4
    =SORTBY(UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,C12:C15,C18:C21)&"</m></x>","//m")),SUMIFS(L12:L21,C12:C21,UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,C12:C15,C18:C21)&"</m></x>","//m"))),-1)

    or with LET function

    =LET(u,UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,C12:C15,C18:C21)&"</m></x>","//m")),SORTBY(u,SUMIFS(L12:L21,C12:C21,u),-1))


    D4:L4
    =SUMIFS(D12:D21,$C$12:$C$21,$C4#)
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    131

    Re: Dynamic overview table that sorts automatically/refreshes based on changes in values.

    Thank you very much bo_ry and all of you for the provided solutions.

+ 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: 3
    Last Post: 12-03-2019, 12:22 AM
  2. If Pivot table refreshes automatically?
    By amreen.nvcc in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-10-2016, 04:53 AM
  3. [SOLVED] Macro to copy data from multiple sheets to overview and align results on overview sheet
    By McBree in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-04-2015, 04:01 PM
  4. Resoruce overview - Create new sheet and add to sumif function in exsisting overview
    By Martinbif in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-27-2013, 09:58 AM
  5. [SOLVED] Line plot which automatically sorts values from lowest to highest
    By excelactuary in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-19-2013, 05:27 AM
  6. Dynamic list based on values on a table
    By adrianodl in forum Excel General
    Replies: 2
    Last Post: 08-21-2012, 11:55 PM
  7. Stopping cells from automatically refreshing after external data refreshes
    By UNWATCHABLE in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-30-2010, 11: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