+ Reply to Thread
Results 1 to 6 of 6

Section wise summarize the data into one sheet from other work sheets

  1. #1
    Registered User
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    81

    Section wise summarize the data into one sheet from other work sheets

    Hi,


    I required a formula help (Sum product or sumifs with Indirect function) to prepare a summary section wise separately by referring the "Ref Number",

    values to be return automatically once entered Ref Number in cells Column A of summary sheet with including related descriptions against ref numbers.

    Sample workbook enclosed for reference.


    Thanks in advance,

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,946

    Re: Section wise summarize the data into one sheet from other work sheets

    As you are using the 365 version of Excel I suggest utilizing Get & Transform (Power Query) to produce a row over row table as modeled on the PQ Table sheet.
    First convert all of the ranges (A2:F11) on the daily sheets to tables (I recommend giving them meaningful names i.e. Table01.09.20).
    The Power Query advanced editor code used to produce the table is:
    Please Login or Register  to view this content.
    Note that the above code will find all of the tables in the workbook.
    The formula for Ref Number is: =IFERROR(INDEX(Query1[Number],MATCH(0,INDEX(COUNTIF(A$3:A3,Query1[Number]),,),)),"")
    The formula for Work Done and the first Total Qty is: =IF($A4="","",INDEX(Query1[Under Section],MATCH($A4,Query1[[Number]:[Number]],0)))
    The formula for Man Power and Machinery is: =SUMIFS(Query1[Man Power],Query1[[Number]:[Number]],$A4,Query1[[Section]:[Section]],$D$1)
    I don't understand what you want for the second the third Qty columns.
    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.

  3. #3
    Registered User
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    81

    Re: Section wise summarize the data into one sheet from other work sheets

    Hi,

    Thanks a lot for your power query solution.

    However this will be a use full solution, question here is this will work on large data like full month date wise sheets as i showed in sample sheet.

    As you asked above about second and third QTY columns, in a work sheet there are 3 sections as like (Section A and B,C),as based on section by referring the Ref number sum values to be return equally each section wise. as like in each section returns sums values of Manpower and Machinery the same way QTY sums values also returns each section wise.


    Thanks,

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,946

    Re: Section wise summarize the data into one sheet from other work sheets

    It should work with any number of tables in a workbook.
    The formula for the Qty columns could be similar to: =IF($A4="","",SUM(D4:E4))
    Let us know if you have any questions.

  5. #5
    Registered User
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    81

    Re: Section wise summarize the data into one sheet from other work sheets

    Hi,

    good to know about it should work many number of sheets in a workbook, its a automate process, once only we can arrange in proper sequence.

    actually i am looking a formula for Column Total quantity in summary here is:=IF($A5="","",SUMIFS(Query1[Qty],Query1[[Number]:[Number]],$A5,Query1[[Section]:[Section]],$D$1))

    in Summary Sheet Section and Ref number wise sum the Qty Column from PQ TABLE.

    any how solution done.


    Thanks for your support.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,946

    Re: Section wise summarize the data into one sheet from other work sheets

    any how solution done...Thanks for your support.
    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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. [SOLVED] Auto creation of day-wise tabs(work-sheets) after updation date in specific cell
    By RavindraK in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-14-2018, 11:06 PM
  2. [SOLVED] How to get summarize data from Various Sheets in a single sheet
    By purav82 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-28-2017, 09:08 AM
  3. [SOLVED] need to arrange column wise data to row wise with monthly wise
    By alok.gupta4ever in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-03-2016, 11:11 AM
  4. [SOLVED] Adding sets of data from different work sheets into a third work sheet.
    By leele2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-12-2013, 01:59 AM
  5. [SOLVED] I want change the data row wise to coloum wise & coloumn wise to row wise.
    By satputenandkumar0 in forum Excel General
    Replies: 3
    Last Post: 12-20-2012, 08:34 AM
  6. macro to compare 2 sheets row wise and copy unmatched data to 3rd sheet
    By prachi b in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-07-2012, 01:46 AM
  7. Summarize data from different sheets in the last sheet
    By maxfesca in forum Excel General
    Replies: 1
    Last Post: 03-25-2008, 01:08 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