Get Data Function - Excel

Question

Hi I am hoping someone can help me.

I have x12 individual excel spreadsheets one for each month. Each spreadsheet contains x8 worksheets (one for each employee) all laid out exactly the same detailing staff Holiday, Sick, Unpaid Leave, Bereavement Leave- by employee.

Each spreadsheet has a summary worksheet page that collects the data from each employee form/spreadsheet (Jan, Feb, March etc) This is a table and collects data using the =sheetname!Q48.(Q48) being the cell to return the data to.

I have created a new additional spreadsheet to collect the data from the 12 spreadsheets summaries and put them into one spreadsheet. (Holiday, Sick, Unpaid Leave, Bereavement Leave). The summary is in a table.

Using the GET DATA function in excel, the data is pulling all the data from each spreadsheet into one spreadsheet. Great.

However it is not pulling all the information through. It is only pulling the dates through for holiday days taken and sick dates taken, but not for the two other categories: Sick and Bereavement dates.

Detailed below is a image of the summary table for Nov, Dec, Jan. The data is being pulled through using the get data function.

Nov Sick Days are working but the dates are not pulling through. Sometimes value is 0 and other times it is blank.

Nov bereavement Number of days is working fine but the dates are not pulling through.

Similar story for Dec. The Number of sick days are fine being pulled through but again no dates.

Its really odd that some have blank and others pull through a zero and then the date column is fine as is the Unpaid leave dates and days.

Thanks

Answer 1

Hi,

Follow the steps in this video to create a single table from multiple worksheets/workbooks - Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks - YouTube