Import Data Question Scenario

[t0ny84]

Hi All,

The following is a query which a colleague has raised with me and I am not even certain that this might be possible, there currently are no spreadsheets in place just the pictures\ideas below.

I am wondering if this may be possible (most likely with VBA) and if so can anyone point me in the right direction. Two spreadsheets, Workbook 1 which employees have access to and use to enter their leave dates in (layout as below) and Workbook 2 which the manager has access to, this workbook only has a simple Table (layout below). My idea is to have a macro which searches through the Workbook 1 and adds the Date - Employee Name - Reason to the table in Workbook 2 whenever a button is pushed.

Workbook 1 - Includes Spreadsheet which lists employees names in column A then across the page it has each date.

Workbook 2 - A simple page with a table (Table1) which has columns for the date, employee name and reason.

Thanks t0ny84

 

[GraH]

If you have Power Query, Get & Transform, the action is a simple unpivot. Rough guidance :

1. Get data from file Excel workbook.
2. Select the workbook and sheet from the navigator
3. Remove top rows 2
4. Promote headers
5. Select first column and unpivot other columns.
6. Save and load as table.