bhammer said:
Seems like a simple (probably common) matter with Excel: a few project
managers each keep their own worksheet of projects showing tasks and statuses
in a common column format, although rows may be added or deleted as projects
come and go. The Boss wants one Master worksheet with tabs for each manager,
so he/she can view--at a glance (and read-only)--everyone's projects' status.
A fine example of when dedicated specific purpose (in this case
project management) software is better than spreadsheets. Even general
database software would be better than spreadsheets. But you could do
this sensibly in Excel by adopting database methods. First, separate
data entry from data presentation. Add all new projects to the BOTTOM
of the source list in each manager's separate workbook, but use
another worksheet in those workbooks to display the records from the
source list in whatever order and subject to whatever filters the
manager wants. So no insertion/deletion of rows/records in the source
list just to show records in the order the manager wants.
Then have the master workbook pull in records from the source lists in
each of the separate workbooks, but this is better done using INDEX
calls than direct cell addressing, e.g.,
[MASTER.XLS]Summary!X99:
=INDEX([Separate001.xls]ProjectSource!$1:$65536,ROWS($A$1:X99),COLUMNS
($A$1:X99))
would be more robust than
=[Separate001.xls]ProjectSource!X99
This is also the better way to handle collecting records from multiple
source workbooks. For example, if the source lists in each separate
workbook started (i.e., top-left) in cell A5, and there were 4 such
separate workbooks to be summarized in MASTER.XLS, use a table in
another range in MASTER.XLS with formulas giving the number of records
in each separate workbook's source list. For example, in AC2:AE5
(named TBL_RECCNT),
Workbook Separate Record Count Cumulating Record Count
Separate001 =MATCH(1E307,[Separate001.xls]ProjectSource!$A$5:$Z$5000)
1
Separate002 =MATCH(1E307,[Separate002.xls]ProjectSource!$A$5:$Z$5000)
=AE2+AD2
Separate003 =MATCH(1E307,[Separate003.xls]ProjectSource!$A$5:$Z$5000)
=AE3+AD3
Separate004 =MATCH(1E307,[Separate004.xls]ProjectSource!$A$5:$Z$5000)
=AE4+AD4
Then collecting records from all separate worksheets using formulas
like
[MASTER.XLS]ProjectSource!A5:
=INDEX(CHOOSE(MATCH(ROWS(A$5:A5),INDEX(TBL_RECCNT,0,3)),
[Separate001.xls]ProjectSource!$A$5:$Z$5000,[Separate002.xls]
ProjectSource!$A$5:$Z$5000,
[Separate003.xls]ProjectSource!$A$5:$Z$5000,[Separate004.xls]
ProjectSource!$A$5:$Z$5000),
ROWS(A$5:A5),COLUMNS($A5:A5))
which will pull in records from each of the separate workbooks in
sequence with no blank rows (if there are no blank rows in the
separate workbooks' source lists, which there SHOULDN'T be if you set
them up correctly).
So the Master simply links and displays each of the manager's worksheets on
a tab in the Master. Whatever the data--whatever the format--no formulas, by
the way, just text (that may make it easier). Just a snapshot of read-only
display of whatever is in the other worksheets.
Just constant cell contents but with formatting? The only way this
could work relatively automatically would be using VBA to open each of
the separate workbooks, copy the source lists from those workbooks and
paste SPECIAL into the next empty row in MASTER.XLS, pasting VALUES
first then pasting FORMATS. If this is really all you want, somewhere
in MASTER.XLS name a cell TBL_SourceFiles, enter the FULL pathnames of
these files beginning in the cell named TBL_SourceFiles and the cells
below it, then use a macro like the following (assumed to be in
MASTER.XLS).
Sub foo()
Dim n As Long, wb As Workbook, twbn As Range, sr As Range, dr As
Range
Set dr = ThisWorkbook.Worksheets("ProjectSource").Range("A5:Z5")
If Application.WorksheetFunction.CountA(dr) > 0 Then
With dr.Resize(dr.Cells(1, 1).End(xlDown).Row + 1 - dr.Row,
dr.Columns.Count)
.ClearContents
.ClearFormats
End With
End If
Set twbn = ThisWorkbook.Names("TBL_SourceFiles").RefersToRange.Cells
(1, 1)
Do While Not IsEmpty(twbn.Value2)
Set wb = Workbooks.Open(Filename:=twbn.Value2)
Set sr = wb.Worksheets("ProjectSource").Range("A5:Z5000")
If IsEmpty(sr.Cells(4996, 1)) Then
Set sr = sr.Resize(sr.Cells(4996, 1).End(xlUp).Row + 1 - sr.Row,
sr.Columns.Count)
ElseIf Not IsEmpty(sr.Cells(4996, 1).Offset(1, 0)) Then
Set sr = sr.Resize(sr.Cells(4996, 1).End(xlDown).Row + 1 -
sr.Row, sr.Columns.Count)
End If
sr.Copy
dr.PasteSpecial Paste:=xlPasteValues
dr.PasteSpecial Paste:=xlPasteFormats
Set dr = dr.Offset(sr.Rows.Count, 0)
wb.Close SaveChanges:=False
Set wb = Nothing
Loop
End Sub
Sounds simple enough, and a common business task, isn't it?
Very common business task, but much, much better handled using a
database than a spreadsheet, and even better handled using project
management software. IMO, it should be a lot of work to use
spreadsheets to do this.
