intersection operator across workbooks?

  • Thread starter Thread starter BrienDownie
  • Start date Start date
B

BrienDownie

I've got regions columns and rows named by their top-most or left-most
name. I.e. "Site1Total" for a row, and "MarchCost" for a column. So,
when I do a cell "=Site1Total MarchCost" it shows me the total cost
for Site 1 in march. Is there a way to reference those intersections
across workbooks?

thanks,
-bld
 
The intersection operator is just like any other operator.

You can use it across WBs, BUT ... you'll need to tell XL where to find the
WB.

This means including the full path together with the named ranges.

It's easiest to have both WBs open, create your link by navigating between
WBs, and then close the source WB.
Build on the links that XL automatically creates in these circumstances.
 
But when I have =SUM('ActualizationPlan--Flowchart.xls'!SiteTotal
JuneProjected) where sitetotal is the row and juneprojected is the
column, it gives me a #NAME? error. thoughts?
 
But when I have =SUM('ActualizationPlan--Flowchart.xls'!SiteTotal
JuneProjected) where sitetotal is the row and juneprojected is the
column, it gives me a #NAME? error. thoughts?
Click to expand...

ok, so i figured it out... just need ot have the full path name before
each named range. i.e. 'filepath.xls
 
BrienDownie said:
ok, so i figured it out... just need ot have the full path name before
each named range. i.e. 'filepath.xls
Click to expand...

You shouldn't need SUM, but the formula will only work when these
other workbooks are open because intersection is only applicable to
ranges, and as far as Excel is concerned, ranges only exist in OPEN
workbooks.
 
Not so Harlan!

Intersection operator works fine on closed WBs, using (referencing) named
ranges in the closed books.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

BrienDownie said:
ok, so i figured it out... just need ot have the full path name before
each named range. i.e. 'filepath.xls
Click to expand...

You shouldn't need SUM, but the formula will only work when these
other workbooks are open because intersection is only applicable to
ranges, and as far as Excel is concerned, ranges only exist in OPEN
workbooks.
 
RagDyeR said:
Not so Harlan!

Intersection operator works fine on closed WBs, using (referencing)
named ranges in the closed books.
Click to expand...
....

You're right, in part. Intersection seems to work without names too as
long as the ranges are single area. Which means intersection of single
area ranges may be the only operation on ranges that works with closed
workbooks.

OTOH, intersection in which one or more of the ranges are multiple
area doesn't work.
 
Would you define "multiple area"?

I have *no trouble* using the intersection operator on closed WBs with
either of these ranges, whether named or literal addresses:

=(Full Path)!D15:I15 (Full Path)!F11:F20

=Sum(Full Path)!D15:I16 (Full Path)!F11:G20)
Of course, without a function included (Sum, Average, ...etc.) to work on
the array of returned values, the 2x110 X 2x6 ranges return a #Value! error.
 
You in a bad mood Harlan?

You should know I'm talking about YOUR use of the phrase, in the context of
this thread.


--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

RagDyer said:
Would you define "multiple area"?
Click to expand...
....

Read online help for the AREAS worksheet function.
 
RagDyeR said:
You in a bad mood Harlan?
Click to expand...

Dunno. You Excel terminology-challenged today?
You should know I'm talking about YOUR use of the phrase, in the context of
this thread.
Click to expand...

From the online help topic I suggested, 'AREAS((B2:D4,E5,F6:I9))
equals 3'. Now do you understand?

In a workbook named deleteme.xls with a single worksheet named
deleteme, enter numbers in every cell in A1:D6. Define the names foo
and bar referring, respectively, to

=deleteme!$A$2:$D$2,deleteme!$A$5:$D$5

and

=deleteme!$B$1:$B$6

With deleteme.xls open, enter the following formula in another
workbook.

=COUNT(deleteme.xls!foo deleteme.xls!bar)

The formula returns 2. Also, in another cell enter the formula

=COUNT((deleteme.xls!$A$2:$D$2,deleteme.xls!$A$5:$D$5) deleteme.xls!$B
$1:$B$6)

and it also returns 2.

Now close deleteme.xls and recalc. The first formula now returns 0 but
the second still returns 2. (Odder and odder.) Excel can't handle the
name foo which refers to a 2-area range, but it can handle the
explicit multiple area range reference.
 
Yes ... very interesting Harlan.

But it just goes to verify that the intersection operator *will work* across
closed WBs ... *under specific conditions*!

With the OP's feed-back of success (using names), we can surmise that his
ranges were not multi-area ranges.

Another item to add to my "memory bank".<bg>
 
Back
Top