+ Reply to Thread
Results 1 to 16 of 16

Get data from multiple sheets at Summary Sheet

  1. #1
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2024
    Posts
    1,390

    Get data from multiple sheets at Summary Sheet

    Hi,

    Currently I am facing an issue with compiling the data from multiple sheets at 'Summary' Sheet.Here the problem is that I want Column A (NAME) from multiple sheets which will be unique and no duplicates.Secondly I want to pull the data of the column which has header 'Net Pay' but the problem is that Column is not fixed and may vary its position. In some sheets it is Column Y, sometimes Z,AB,AA and so on.

    So,how can this be done ?

    Here sheet name can be shown in row.Sample header and column A data is shown manually in 'Summary' sheet including Total of 'Net Pay' at last.

    Pls note that there are many sheets may be 50-60.Here in this demo file I have presented only few sheets for demonstration purposes.
    Attached Files Attached Files
    Last edited by paradise2sr; 06-30-2025 at 12:27 PM. Reason: adding more information

  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
    31,252

    Re: Get data from multiple sheets at Summary Sheet

    Please Login or Register  to view this content.
    NOTE: formula in column A to get unique list of names

    Added a "Start" and "End" sheet to delimit the "pay" Sheets"

    UPDATE: add TMS formula solution in sheet SUMMARY(2)
    Attached Files Attached Files
    Last edited by JohnTopley; 06-30-2025 at 03:29 PM.
    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
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,042

    Re: Get data from multiple sheets at Summary Sheet

    This formula, dragged across and down should work:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    And it does, for the first five columns, but not the last five. Removing the INDIRECTs doesn't help so I can only think the structure of the data is different somehow. Struggling to work out what the issue is.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  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
    31,252

    Re: Get data from multiple sheets at Summary Sheet

    @tms:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,042

    Re: Get data from multiple sheets at Summary Sheet

    Quote Originally Posted by JohnTopley View Post
    @tms:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thank you JT:

    That first Z should have been ZZ. But you have highlighted the errors of my ways .

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2024
    Posts
    1,390

    Re: Get data from multiple sheets at Summary Sheet

    There is compatibility issue with the formula may be with using Start:END.However,I have all the functions such as SORT,UNIQUE,FILTER,VSTACK,LAMBDA

    LET(r,Start:END!$A$6:$A$90,SORT(UNIQUE(FILTER(VSTACK(r),VSTACK(r)>0))))

    This I want if possible to define last data to be taken not as a specific range but instead to be taken as last continuous non spaces starting from A6.In sheet 04.01 last continuous non spaces is A90 but pls note that there is data at A93 and below which is not to be taken as last continuous non spaces ends at A90.

    Secondly,D3,D4 across,I have mentioned manually but for more than 50 sheets it is not possible by manually.

  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
    31,252

    Re: Get data from multiple sheets at Summary Sheet

    There is compatibility issue with the formula may be with using Start:END.


    LET(r,Start:END!$A$6:$A$90,SORT(UNIQUE(FILTER(VSTACK(r),VSTACK(r)>0))))
    While I recognise the need for flexibilty in the formula, you need to recognise this means testing (via VBA) all 50/60 sheets to construct a unique list; it would be better/simpler to make the tables a consistent length of the likely maximum number of (name) entries. And the number of columns in each table is not consistent but has been addressed by both the VBA and the formula.

    It is bad prectice to put tables one beneath the other .e.g the OT and Print tables: not least as this presents problems as illustrated here.

    Secondly,D3,D4 across,I have mentioned manually but for more than 50 sheets it is not possible by manually.
    What is the diificulty is dragging/copying a formula across 50 columns? a one-off exercise!

    Added macro to get unique names list and TMS macro from post #12 to add formula solution.


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by JohnTopley; 07-02-2025 at 05:10 AM.

  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
    31,252

    Re: Get data from multiple sheets at Summary Sheet

    @TMS: thanks for rep but totally unwarranted!

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,042

    Re: Get data from multiple sheets at Summary Sheet

    @JT: you see what you expect to see. I could have been looking at the formula and the data for hours, if not days, and not picked up on the error. And, it would have been an ongoing source of irritation if I hadn't been able to fix it. So, you saved me from that .

  10. #10
    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
    31,252

    Re: Get data from multiple sheets at Summary Sheet

    @TMS: we have all been there ... probably more times than we care to admit!

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,042

    Re: Get data from multiple sheets at Summary Sheet

    @JT: Oh yes, but a second pair of eyes often identifies the issue(s) in seconds!

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,042

    Re: Get data from multiple sheets at Summary Sheet

    Code to drop the formula into the matrix

    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2024
    Posts
    1,390

    Re: Get data from multiple sheets at Summary Sheet

    Quote Originally Posted by JohnTopley View Post




    While I recognise the need for flexibilty in the formula, you need to recognise this means testing (via VBA) all 50/60 sheets to construct a unique list; it would be better/simpler to make the tables a consistent length of the likely maximum number of (name) entries. And the number of columns in each table is not consistent but has been addressed by both the VBA and the formula.

    It is bad prectice to put tables one beneath the other .e.g the OT and Print tables: not least as this presents problems as illustrated here.



    What is the diificulty is dragging/copying a formula across 50 columns? a one-off exercise!

    Added macro to get unique names list and TMS macro from post #12 to add formula solution.


    Please Login or Register  to view this content.
    Thank you John.It seems to work.I request to do generate sheet name automatically at D3:3 and do the rest work with stacking 'Total' at last doing sum of each column.

  14. #14
    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
    31,252

    Re: Get data from multiple sheets at Summary Sheet

    Please Login or Register  to view this content.
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2024
    Posts
    1,390

    Re: Get data from multiple sheets at Summary Sheet

    Thank you very much John💖💖💖💖💖.

    By the way why you have given 3 blank rows and stacked 'Total'.No needed to give blank rows.
    Last edited by paradise2sr; 07-03-2025 at 11:33 PM.

  16. #16
    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
    31,252

    Re: Get data from multiple sheets at Summary Sheet

    Please Login or Register  to view this content.
    Change as required

+ 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. Auto-populate front sheet or summary sheet with data from multiple tabs or sheets
    By cmann89 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-05-2021, 05:42 AM
  2. Replies: 3
    Last Post: 11-10-2018, 06:59 AM
  3. Summary Sheet - Using data from multiple sheets
    By disco dave in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-01-2015, 01:48 PM
  4. [SOLVED] Combining data across multiple sheets onto one summary sheet
    By dajul73 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 07-29-2014, 02:10 PM
  5. Replies: 0
    Last Post: 07-29-2013, 03:00 AM
  6. [SOLVED] Summary sheet - copy cell contents from multiple sheets to one summary sheet
    By jsmity in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-21-2013, 10:32 AM
  7. Copying a template sheet and putting those multiple sheets data into a summary sheet
    By John Wolfe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-18-2011, 06:29 PM

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