Consolidate data from multiple workbooks in a Master Sheet

[prataplata]

Hello,

Every month, each individual territories share their sales numbers to me.
I have data in multiple workbooks. All workbooks have the same format. The data is only numbers in all the sheets. Only the column headers are values. I want to create a master sheet which will simply show the sum of all the data in each individual workbooks. The format of the Master Workbook is also same as the individual workbooks.

I have tried using basic formula giving cell reference of the cell and adding them. Something like

'C:\CVC Formats\Praptra 1\[Prapatra 1_Ahmednagar.xlsx]Sheet1'!K698+
'C:\CVC Formats\Praptra 1\[Prapatra 1_Akola.xlsx]Sheet1'!K698+
'C:\CVC Formats\Prapatra 1\[Prapatra 1_Amravati.xlsx]Sheet1'!K698

However I am sure that there can be a better way to do this. Also, this makes the master file too long to load.

Someone please share any code/macro that can be used.

Any help is much appreciated.

 

[sandy666]

Try PowerQuery to consolidate all workbooks into one

PowerQuery is NOT vba or classic formula
Excel Power Query (Get & Transform) Tutorial for Beginners

 

[prataplata]

I have installed Power Query. However, being a relative newbie cant get my way around it. Is there a step-by-step solution to my problem

Thanks.

 

[sandy666]

generally:
load all tables what you need from external files into one, main sheet
then you can append all these tables and transform as you wish to achieve what you want
You need to read more about PowerQuery to use it easy

• Getting Started with Get & Transform in Excel
• Microsoft Power Query for Excel Help

 

[sandy666]

or you can try PivotTable from different files (Alt+D+P) into one