Summarize data from multiple tabs on another workbook

[Jason McDaniel]

I have a workbook that is updated and sent to me every day by another company.  This workbook contains a new tab that is created for each day with the date as the worksheet name.  Each worksheet has columns A through H and an unspecified number of rows (usually not more than 10).  I would like to create another workbook that takes this information and puts it all on one worksheet with the date (worksheet name) in the first or last column of each row.

[#2]

I wish all the members of this community would ask their questions as you did!
A clear and detailed explanation of the request!

You just need to Get and Transform Data in Excel 2016 (aka Power Query in earlier versions of Excel).

By using Power Query, you can append all datasets in each worksheet into a single worksheet.

Before that, you need to convert each dataset to a table and to create a new column in each table contains the worksheet name.

I leave you with this video to learn all these stuff!

[#3]

To clarify:

1. The file always has the same name?
2. New sheets are added for each day?
3. Existing sheets are not deleted?

[#4]

Hi Jason,

I have a similar situation but different question.

Can anyone help me, as we can do this with vlookup but it can only lookup one worksheet and not entire workbook.

I have a file (named after the month).  This workbook, has worksheets named after the 'dates' in the month.

I want to lookup/ find an order# (from different xls file) in the 'month' workbook (all the sheets in the other workbook) and the result I need it to tell me is the date provided in the column within that worksheet.

So, e.g if order # 342 is found in sheet/ tab '21' and '31', I want it to give me this result in the 'cell', where i have list of order numbers.

If I have 500 order #, i want to know in which date these order nos are found in the another file/ workbook.

I hope my issue is easy to undersand.

[#5]

Mark,

Yes, the file always has the same name.  A new sheet is added each day and existing sheets are not deleted.