making multiple sheets into one master list

%3CLINGO-SUB%20id%3D%22lingo-sub-1812268%22%20slang%3D%22en-US%22%3Emaking%20multiple%20sheets%20into%20one%20master%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1812268%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20working%20on%20a%20very%2C%20very%20simple%20task%20of%20tracking%20all%20projects%20in%20my%20organization.%26nbsp%3B%20I%20have%20created%20multiple%20sheets%20for%20each%20Architect%20with%20a%20list%20of%20all%20their%20current%20projects.%26nbsp%3B%20Now%2C%20what%20I%20want%20to%20do%20is%20make%20all%20these%20separate%20sheets%20populate%20into%20one%20master%20list%2C%20that%20updates%20automatically%20to%20show%20the%20sum%20of%20all%20projects%20within%20the%20organization.%20There%20are%20no%20formulas%2C%20etc.%20in%20my%20sheets%20-%20so%20I%20know%20this%20should%20be%20very%20easy%2C%20but%20I%20do%20not%20know%20how%20to%20do%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKiva%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1812268%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1812499%22%20slang%3D%22en-US%22%3ERe%3A%20making%20multiple%20sheets%20into%20one%20master%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1812499%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F843578%22%20target%3D%22_blank%22%3E%40kivaconnor%3C%2FA%3E%26nbsp%3BYou%20can%20try%20powerquery%20to%20join%20(append)%20all%20those%20separate%20worksheets.%20I'd%20rather%20use%20a%20list%20in%20sharepoint%20though%2C%20if%20you%20subscribe%20to%20Office365%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fmicrosoft-365%2Fmicrosoft-lists%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fmicrosoft-365%2Fmicrosoft-lists%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1814922%22%20slang%3D%22en-US%22%3Etry%20kutools%20or%20wps%20toolbox%20to%20merge%20table%20data(multiple%20worksheets)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1814922%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F843578%22%20target%3D%22_blank%22%3E%40kivaconnor%3C%2FA%3E%26nbsp%3BThere%20are%20several%20ways%20to%20merge%20table%20data%20as%20your%20request.%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3E1st%20Method%26nbsp%3B%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CP%3E1.Try%20excel%20extended%20tools%20such%20as%26nbsp%3B%5BKutools%20for%20Excel%3A%20Powerful%20Excel%20Toolbox%5D(%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fproduct%2Fkutools-for-excel%2Fproduct-tutorials.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.extendoffice.com%2Fproduct%2Fkutools-for-excel%2Fproduct-tutorials.html%3C%2FA%3E).%20In%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F456-combine-multiple-workbooks.html%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eblog%3C%2FA%3E%20kutools%20provide%20at%20least%20three%20ways%20to%20get%20the%20master%20list.%3C%2FP%3E%3CUL%3E%3CLI%3EEasily%20Combine%20Multiple%20Workbooks%20Or%20Specified%20Sheets%20Of%20Workbooks%20To%20One%20Workbook%3C%2FLI%3E%3CLI%3ECombine%20Multiple%20Workbooks%20Or%20Specified%20Sheets%20Of%20Workbooks%20To%20A%20Master%20Workbook%20With%20VBA%3C%2FLI%3E%3CLI%3ECombine%20Multiple%20Workbooks%20Into%20One%20Workbook%20With%20Move%20Or%20Copy%20Function%3C%2FLI%3E%3C%2FUL%3E%3CP%3EIn%20your%20case%2C%20I%20think%20the%20easy%20combine%20could%20work%20well%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3E2nd%20Method%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E2.%20Try%20other%20office%20application%20such%20as%20%3CA%20href%3D%22https%3A%2F%2Fwww.wps.com%2Fmac%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewps%3C%2FA%3E.%26nbsp%3BI%20use%20WPS%20to%20get%20master%20list%20from%20merging%20table%20data%20in%20just%20four%20steps.%20I%20put%20my%20steps%20in%20images%20as%20below%2C%20hope%20it%20helped.%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%3CSTRONG%3EStep1.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3EOpen%20WPS%20software%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%3CSTRONG%3EStep2.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3EOpen%20workbook%20which%20contains%20multipule%20worksheets%20which%20ready%20to%20be%20merged.Mine%20contains%20three%20sheets%20with%20same%20title%20row.%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3Esheet1%3AAlice%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202020-10-24%20at%2010.20.34%20PM.png%22%20style%3D%22width%3A%20388px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F229044i484BB481C9A45037%2Fimage-dimensions%2F388x232%3Fv%3D1.0%22%20width%3D%22388%22%20height%3D%22232%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202020-10-24%20at%2010.20.34%20PM.png%22%20alt%3D%22Screen%20Shot%202020-10-24%20at%2010.20.34%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3Esheet2%3ABob%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202020-10-24%20at%2010.20.47%20PM.png%22%20style%3D%22width%3A%20218px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F229045iD941C3F57A64A9BB%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202020-10-24%20at%2010.20.47%20PM.png%22%20alt%3D%22Screen%20Shot%202020-10-24%20at%2010.20.47%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3Esheet3%3ACindy%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202020-10-24%20at%2010.20.56%20PM.png%22%20style%3D%22width%3A%20193px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F229046i365BE9BE2CE014DA%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202020-10-24%20at%2010.20.56%20PM.png%22%20alt%3D%22Screen%20Shot%202020-10-24%20at%2010.20.56%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3EStep3.%20Use%20toolbox%20to%20merge%20table.Click%20%3CSTRONG%3Etoolbox%3C%2FSTRONG%3E%20on%20the%20top%20bar.%20Click%20%3CSTRONG%3EMerge%20Tables%3C%2FSTRONG%3E.%20Click%20%3CSTRONG%3EMerge%20worksheets%3C%2FSTRONG%3E.%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22toolbox.png%22%20style%3D%22width%3A%20969px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F229048i296B6BDA66E2A7E8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22toolbox.png%22%20alt%3D%22toolbox.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3EStep%204.%20Select%20worksheets%20needed%20to%20be%20merged.%20Select%20%3CSTRONG%3Etitle%20row%20for%201%3C%2FSTRONG%3E%20which%20is%20quite%20important.%20Select%20%3CSTRONG%3E%3Aart%20mergin%3C%2FSTRONG%3E.You%20may%20take%20a%20look%20at%20the%20snapshot%20when%20I%20make%20my%20selection.%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mergetable.png%22%20style%3D%22width%3A%20544px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F229049iF67AB6A8252A9192%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22mergetable.png%22%20alt%3D%22mergetable.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%3CSTRONG%3EMaster%20List%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1814927%22%20slang%3D%22en-US%22%3ERe%3A%20try%20kutools%20or%20wps%20toolbox%20to%20merge%20table%20data(multiple%20worksheets)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1814927%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F843578%22%20target%3D%22_blank%22%3E%40kivaconnor%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20how%20the%20merge%20table%20looks%20like.%20I%20think%20it%20can%20be%20handful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mergetalbe1.png%22%20style%3D%22width%3A%20451px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F229052iB6E872114129F39F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22mergetalbe1.png%22%20alt%3D%22mergetalbe1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1812740%22%20slang%3D%22en-US%22%3ERe%3A%20making%20multiple%20sheets%20into%20one%20master%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1812740%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494140%22%20target%3D%22_blank%22%3E%40wsantos%3C%2FA%3E%26nbsp%3Bthank%20you!%26nbsp%3B%20I%20appreciate%20both%20suggestions%20very%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I'm working on a very, very simple task of tracking all projects in my organization.  I have created multiple sheets for each Architect with a list of all their current projects.  Now, what I want to do is make all these separate sheets populate into one master list, that updates automatically to show the sum of all projects within the organization. There are no formulas, etc. in my sheets - so I know this should be very easy, but I do not know how to do it.

 

thanks!

 

Kiva

4 Replies
Highlighted

@kivaconnor You can try powerquery to join (append) all those separate worksheets. I'd rather use a list in sharepoint though, if you subscribe to Office365: https://www.microsoft.com/en-us/microsoft-365/microsoft-lists.

 

Highlighted

@wsantos thank you!  I appreciate both suggestions very much!

Highlighted

@kivaconnor There are several ways to merge table data as your request.

1st Method 

1.Try excel extended tools such as [Kutools for Excel: Powerful Excel Toolbox](https://www.extendoffice.com/product/kutools-for-excel/product-tutorials.html). In this blog kutools provide at least three ways to get the master list.

  • Easily Combine Multiple Workbooks Or Specified Sheets Of Workbooks To One Workbook
  • Combine Multiple Workbooks Or Specified Sheets Of Workbooks To A Master Workbook With VBA
  • Combine Multiple Workbooks Into One Workbook With Move Or Copy Function

In your case, I think the easy combine could work well for you.

 

2nd Method

2. Try other office application such as wps. I use WPS to get master list from merging table data in just four steps. I put my steps in images as below, hope it helped.

 

Step1.

Open WPS software

 

Step2.

Open workbook which contains multipule worksheets which ready to be merged.Mine contains three sheets with same title row.

 

sheet1:Alice

Screen Shot 2020-10-24 at 10.20.34 PM.png

sheet2:Bob

Screen Shot 2020-10-24 at 10.20.47 PM.png

sheet3:Cindy

Screen Shot 2020-10-24 at 10.20.56 PM.png

Step3. Use toolbox to merge table.Click toolbox on the top bar. Click Merge Tables. Click Merge worksheets

 

toolbox.png

 

Step 4. Select worksheets needed to be merged. Select title row for 1 which is quite important. Select :art mergin.You may take a look at the snapshot when I make my selection.

mergetable.png

 

Master List

 

 

 

 

 

Highlighted

@kivaconnor 

This is how the merge table looks like. I think it can be handful.

 

mergetalbe1.png