Hi
When you have an employee either in one or another file (but never in both)
1. Using links:
(This method is applicable only, when there is some max number of possible
entries in both files). Let's assume the number of entries never exceeds 99.
Create a sheet p.e. EmployeeList, with headings in row1.
Into cell A2 in sheet EmployeeList enter the formula (I assume original
employee lists are on sheet Employees in files File1.xls and File2.xls,
which are stored in folder C:\MyPath):
=IF('C:\MyPath\[File1.xls]Employees'!A2="","",'C:\MyPath\[File1.xls]Employees'!A2)
and copy the formula to whole table and down to row 100.
Into cell A101 in sheet EmployeeList enter the formula:
=IF('C:\MyPath\[File2.xls]Employees'!A2="","",'C:\MyPath\[File2.xls]Employees'!A2)
and copy the formula to whole table and down to row 200.
Now you have the list of all employees, although with a lot of empty rows in
it. For some tasks it is enough, for some not.
Easiest way to get a list without gaps is to use an ODBC query to retrieve
non-empty rows into a new table. For this you have to define the table
EmployeeList as a named range, like
EmplList=EmployeeList!$A$1:$C$200
, and save the workbook after that.
Now you create a new sheet p.e. Employees, and create there an ODBC query
from Excel files, where your workbook is ODBC source, and the named range
EmplList is the source table. Set [Employee Number] Is Not Null as WHERE
clause and finish. In query Data Range Properties set the query to be
refreshed on Open(, and/or after some every time unit).
2. Using ODBC queries - it is more complex solution, and I can give only
general directions, as much depends on your data.
(You must have Analysis Toolpack installed, otherwise source workbooks must
be always opened too - it's a mystery for me why)
Define employee lists in both source files like in example above (no dynamic
ranges will work), p.e. EmplList1 and EmplList2. You can freely have a lot
of empty rows in those named ranges ready for future entries. Save source
files and close them.
In destination workbook, create sheets p.e. Employees1 and Employees2. On
sheet Employees1, create an ODBC query from Excel files like above, but
with File1.xls as source, and named range EmplList1 as source table. On
sheet Employees2, create an ODBC query from Excel files like above, but
with File2.xls as source, and named range EmplList2 as source table.
Define 2 named ranges in destination workbook (they too may be p.e.
EmplList1 and EmplList2 too). On 3rd sheet (Employees) create an ODBC query
from table on sheet Employees1 (or from Employees2) like previous queries.
Activate query wizard, and there SQL window. Change the SQL string to
somewhat like
SELECT EmplList1.[Employee Name], EmplList1.[Employee Number],
EmplList1.[Contact Details] FROM EmplList1 EmplList1 WHERE
EmplList1.[Employee Number] Is Not Null
UNION
SELECT EmplList2.[Employee Name], EmplList2.[Employee Number],
EmplList2.[Contact Details] FROM EmplList2 EmplList2 WHERE
EmplList2.[Employee Number] Is Not Null
or
SELECT EmplList1.[Employee Name], EmplList1.[Employee Number],
EmplList1.[Contact Details] FROM EmplList1 EmplList1 WHERE
EmplList1.[Employee Number] Is Not Null
UNION
SELECT EmplList2.[Employee Name], EmplList2.[Employee Number],
EmplList2.[Contact Details] FROM EmplList2 EmplList2 WHERE
EmplList2.[Employee Number] Is Not Null AND EmplList2.[Employee Number] Is
Not In (SELECT [Employee Number] FROM EmplList1)
(The latter is for case some employees are in both source lists)
