linking to a named range in another workbook

[Paul]

Hi:

I thought this would be simple. So far, it isn't.
I need to display a subset of contiguous rows and columns from a
source workbook in a destination workbook. (I've given the range a
name).
The source range will shrink and grow. However the top and bottom
rows will be static as such.
(i.e. the range will grow but not by appending to the last row, it's a
footer.)

Getting the destination to be truely dynamic seems to be the problem.
It seems like allocating additional area for future growth is required
and that area ends up with either #NUM or #VALUE type messages, or
else the 'copy' runs the risk of truncation.

(I don't think I want to use OLE)

Is there an elegant solution?

Thanks in advance, Paul

 

[Harlan Grove]

I need to display a subset of contiguous rows and columns from a
source workbook in a destination workbook. (I've given the range a
name).
The source range will shrink and grow. However the top and bottom
rows will be static as such. (i.e. the range will grow but not by
appending to the last row, it's a footer.)

....

More explanation needed. Do you mean something like you'd always be
pulling a range between, say, ws!A1:J1 and ws!A100:J100 in the other
workbook?

Is there an elegant solution?

Probably not. What is it EXACTLY that you're trying to do? Just
displaying a subrange of another range isn't too difficult, though you
won't be able to refer to dynamic defined ranges in the other workbook
when it's closed.

If you want a subrange of C:\foo\[bar.xls]ws!A1:J100, and if the
beginning and ending rows within that range were found in nondynamic
named ranges in that workbook, say in BegRow and EndRow, then if the
top-left cell in your destimation worksheet were C5, use formulas like

C5:
=IF(ROWS(C$5:C5)>'C:\foo\bar.xls'!EndRow-'C:\foo\bar.xls'!BegRow+1,"",
INDEX('C:\foo\[bar.xls]ws'!A$1:J$100,'C:\foo\bar.xls'!BegRow-1+ROWS(C
$5:C5)))

Fill C5 right into D5:L5, then fill C5:L5 down as far as needed.

 

[Paul]

More explanation needed.

Okay...

Within the middle of one of the sheets in workbook Source will be
'data' I need to pull and consolidate with similar data from other
workbooks. My intent is to consolidate from Source, Source1, Source2,
etc. into a common destination workbook, e.g. Dest. I would want
workbook Dest to recognize any changes (added/deleted rows) that occur
in any of the ranges in source books.

I should mention that rows will be added/deleted ahead of and behind
this section of 'data' as well as within it. The only possible
advantage is that I can indeed tag the BegRow and EndRow as suggested
above because I won't be appending to the very first or last rows in
the range of interest.

Also, the 'data' is not clean data in the sense of proper labels and
guaranteed data in any given cell. So, for example, I can't test for
the first empty cell in column A to determine the end of my data.

In the example below I've given the source data range covering the
rows and columns from Matters to Matters Total the name Stuff and I'd
like that subsection of book Source to be part of book Dest...

Doesn't Matter
1
2

4
5
Doesn't Matter Total

Matters
1

3
4
5
Matters Total

Dont Care
1
2
3
Dont Care Total


Thanks, Paul