External reference to entire tab?

  • Thread starter Thread starter bhammer
  • Start date Start date
B

bhammer

Excel 2003
Three separate .xls files on our office server. One Master .xls file on the
server that references everything on each of the three external files, each
on a separate tab (worksheet).

Need ability to insert/delete rows on separate files without re-doing
references on Master. Also formatting to come along with data.

Possible?
 
bhammer said:
Excel 2003 ....
Need ability to insert/delete rows on separate files without re-doing
references on Master. Also formatting to come along with data.

Possible?
Click to expand...

No.

If the other 3 files could be opened by other users and they were
allowed to insert/delete rows/column at whim, then unless there were
labels in other cells to serve as 'landmarks', it'd be impossible for
MASTER.XLS to keep track of its external references into these other
files.

It may be possible for MASTER.XLS to determine where certain cells
have moved to in the other 3 workbooks, but since you've provided no
details it's impossible to provide possible work-arounds.

As for formatting, formatting generally doesn't follow data, and if
the formatting in question were conditional formatting, then it's not
possible without VBA code in MASTER.XLS opening the other workbooks if
they weren't already open, determining whether conditional formatting
rules applied, and applying the applicable conditional formatting as
regular cell formats in MASTER.XLS.

So, this may be possible if you're an advanced VBA programmer.
Otherwise, not possible.
 
Harlan,

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.

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.

Sounds simple enough, and a common business task, isn't it?
 
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.
Click to expand...

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.
Click to expand...

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?
Click to expand...

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.
 
Harlan,

Thank you for the detailed example. I'll play around with it.

All of my VBA experience is actually in Access, but for some reason I was
hoping to keep an Excel solution (since that's what the other managers are
familiar with now) and not have to migrate them to Access, but maybe it's
worth it. . .

thanks again for brainstorming with me.
 
Back
Top