Technical design thoughts?

[Joe]

Can anyone assist with design thoughts on achieving the
following with excel.

BACKGROUND
Spreadsheet A has an employee no. col and 10 other cols.
Spreadsheet B has an employee no. col and 8 other cols.
Spreadsheet C has an employee no. col and 3 other cols.
NOTE -- The number of employee rows are different within
each spreadsheet and are unsorted.

A new Spreadsheet D is to be created with:
- employee number col
- five cols from spreadsheet A
- six cols from spreadsheet B
- three cols from spreadsheet C.

PROCESSING
Spreadsheet A is issued every month with a current list
of employees and is the basis for spreadsheet D.
For each entry in spreadsheet A create an entry in
spreadsheet D collecting any information for the employee
no. from spreadsheet B and C. if it exists.

Spreadsheet D is to have the current date/time in its
file name. This allows a history of information to be
kept.

An automatic process (macro's?, VB?, template?) would
automatically process current spreadsheets A, B and C and
create D.
Therefore the user would manually trigger this process
whenever a new version of spreadsheets A, B or C was
recieved.

I can see how this would be done manually but I would
like to automate the process, any ideas?

regards

Joe

 

[Arvi Laanemets]

Hi

As I did understand, workbook/spreadsheet D is also monthly, is it?

I myself would prefer a design based on workbook D, which will be always
current. Maybe so:

Workbook A is overwritten every month with fresh employees data. The name
for workbook A will be always same.

In workbook D, you have a worksheet p.e. CurrentMonth, with links to
workbook A. Like
A1=IF('C:\My Documents\[A.xls]Sheet1'!A1="","",'C:\My
Documents\[A.xls]Sheet1'!A1), etc. The range with formulas has to have
enough rows, to link any realistic number of employees list in workbook A
into workbook D.
Into adjacent columns to linked data, you retrieve additional info from
workbooks B and C, using VLOOKUP()
p.e.
M2==IF(ISERROR(VLOOKUP(A2,'C:\My
Documents\[B.xls]Sheet1'!$A$2:$G$100,2,FALSE)),"",VLOOKUP(A2,'C:\My
Documents\[B.xls]Sheet1'!$A$2:$G$100,3,FALSE))

Every month, before overwriting the workbook A1 (and maybe editing workbooks
B and C too), you select the whole datarange on CurrentMonth sheet, copy it,
create an empty worksheet in workbook A, or in some separate archive
workbook, and
PasteSpecial.Values+PasteSpecial.Formats+PasteSpecial.ColumnWidths into new
sheet. And then rename the archived sheet with some meaningful name. For all
this you can easily write a macro, using macro recorder and then editing the
code you get.

When you want to store archived data for every year in separate workbook,
use the original workbook with archived sheets (A.xls, or another) as one
for current year. After you archive December data, SaveAs it with new name,
identifying it as one for past year, and then delete all archive sheets from
original one. The original workbook is ready for new year.


Arvi Laanemets