Issues referencing range between workbooks
 

Hi All,

Maybe I am just missing something really simple (most likely) so
forgive me if I seem a bit slow.

I started of with a single workbook, the first worksheet had a matrix
of figures in it, the next several tabs were filled with formulas and
macros to gather statistics and generate metrics. Due to some
performance issues and because I wanted the first sheet to be able to
be shared across several users, I split up the workbook, having the
Matrix worksheet in the first book (shared), and the other worksheets
in an Admin workbook.

The issue is that if I, the admin, open up the Matrix first, then open
the Admin workbook, all the admin formulas work. HOWEVER!!! If I open
up the Admin workbook alone, all formulas appear as #VALUE!

All admin formulas are using a named range. The range "status" is
specified as:

='C:\Documents and
Settings\[user]\Desktop\Projects\[filename.xls]Worksheet1'!$F$12:$Y$236

An example formula is:

=COUNTIF(status,"p*")

Is this a known issue? Or am I just missing something completely
obvious?

Cheers

Rob

Issues referencing range between workbooks
 

There are functions that don't work with closed files.
=countif(), =sumif(), =indirect() are a few.

Maybe you could use:
=sumproduct(--(left(status,1)="p"))

=sumproduct() won't work with whole columns--but that's not a problem with your
name.


Rob Manger wrote:

Hi All,

Maybe I am just missing something really simple (most likely) so
forgive me if I seem a bit slow.

I started of with a single workbook, the first worksheet had a matrix
of figures in it, the next several tabs were filled with formulas and
macros to gather statistics and generate metrics. Due to some
performance issues and because I wanted the first sheet to be able to
be shared across several users, I split up the workbook, having the
Matrix worksheet in the first book (shared), and the other worksheets
in an Admin workbook.

The issue is that if I, the admin, open up the Matrix first, then open
the Admin workbook, all the admin formulas work. HOWEVER!!! If I open
up the Admin workbook alone, all formulas appear as #VALUE!

All admin formulas are using a named range. The range "status" is
specified as:

='C:\Documents and
Settings\[user]\Desktop\Projects\[filename.xls]Worksheet1'!$F$12:$Y$236

An example formula is:

=COUNTIF(status,"p*")

Is this a known issue? Or am I just missing something completely
obvious?

Cheers

Rob

--

Dave Peterson

Issues referencing range between workbooks
 

Some more info.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Rob Manger wrote:

Hi All,

Maybe I am just missing something really simple (most likely) so
forgive me if I seem a bit slow.

I started of with a single workbook, the first worksheet had a matrix
of figures in it, the next several tabs were filled with formulas and
macros to gather statistics and generate metrics. Due to some
performance issues and because I wanted the first sheet to be able to
be shared across several users, I split up the workbook, having the
Matrix worksheet in the first book (shared), and the other worksheets
in an Admin workbook.

The issue is that if I, the admin, open up the Matrix first, then open
the Admin workbook, all the admin formulas work. HOWEVER!!! If I open
up the Admin workbook alone, all formulas appear as #VALUE!

All admin formulas are using a named range. The range "status" is
specified as:

='C:\Documents and
Settings\[user]\Desktop\Projects\[filename.xls]Worksheet1'!$F$12:$Y$236

An example formula is:

=COUNTIF(status,"p*")

Is this a known issue? Or am I just missing something completely
obvious?

Cheers

Rob

--

Dave Peterson

Issues referencing range between workbooks
 

Hi Dave,

Thanks heaps for clarifying that.

Rob