#Value replaces formula result when file is opened. But why?

[Arlen]

I have a workbook with a formulas referencing 5 other workbooks. Whenever I
sort the results, or close and open it, all results turn into #Value. The
numbers come back when I reopen the workbook being referenced, but really,
there must be something we can do?

Here's one of those formulae:
=COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma
2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error Checking
underlines everything, then just E5...so there.

Also, is there a way to bypass the Update Links screen if the file hasn't
been moved or renamed?

I thank you for your time.

Arlen

 

[Arlen]

I guess what is happening is that, when the workbook with the formulas and
the workbook being reference are open together, the formula is

=COUNTIF('[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5)

but when just the formula sheet is open, the formula turns into

=COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma 2007\
[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5)

 

[T. Valko]

COUNTIF won't work when referencing a closed file. Use SUMPRODUCT.

Have the source file open when you write the formula and then just use your
mouse to point to the source. This way Excel will put all that path junk in
for you. Much easier than typing it!

=SUMPRODUCT(--(really_long_pathC1:C100=E5))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007.

 

[Arlen]

Okay, Biff. One follow up.

If I can't select all of Column C, how can I at least select everything from
C4 to Infinity?
Like C4:C????

Arlen

COUNTIF won't work when referencing a closed file. Use SUMPRODUCT.

Have the source file open when you write the formula and then just use your
mouse to point to the source. This way Excel will put all that path junk in
for you. Much easier than typing it!

=SUMPRODUCT(--(really_long_pathC1:C100=E5))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP

I have a workbook with a formulas referencing 5 other workbooks. Whenever
I
sort the results, or close and open it, all results turn into #Value. The
numbers come back when I reopen the workbook being referenced, but really,
there must be something we can do?

Here's one of those formulae:
=COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma
2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error Checking
underlines everything, then just E5...so there.

Also, is there a way to bypass the Update Links screen if the file hasn't
been moved or renamed?

I thank you for your time.

Arlen

 

[T. Valko]

OK, it sounds like you're not using Excel 2007...so:

C4:C65536

Note that SUMPRODUCT will evaluate *every* cell referenced. So it's in your
best interest to use as small a range as is necessary.

--
Biff
Microsoft Excel MVP

Okay, Biff. One follow up.

If I can't select all of Column C, how can I at least select everything
from
C4 to Infinity?
Like C4:C????

Arlen

COUNTIF won't work when referencing a closed file. Use SUMPRODUCT.

Have the source file open when you write the formula and then just use
your
mouse to point to the source. This way Excel will put all that path junk
in
for you. Much easier than typing it!

=SUMPRODUCT(--(really_long_pathC1:C100=E5))

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP

I have a workbook with a formulas referencing 5 other workbooks.
Whenever
I
sort the results, or close and open it, all results turn into #Value.
The
numbers come back when I reopen the workbook being referenced, but
really,
there must be something we can do?

Here's one of those formulae:
=COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma
2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error Checking
underlines everything, then just E5...so there.

Also, is there a way to bypass the Update Links screen if the file
hasn't
been moved or renamed?

I thank you for your time.

Arlen

 

[Arlen]

Biff,

Perfect! But could you explain what is happening, because I'm gonna need to
use it again with SUMIF and such.

Thanks.

COUNTIF won't work when referencing a closed file. Use SUMPRODUCT.

Have the source file open when you write the formula and then just use your
mouse to point to the source. This way Excel will put all that path junk in
for you. Much easier than typing it!

=SUMPRODUCT(--(really_long_pathC1:C100=E5))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP

I have a workbook with a formulas referencing 5 other workbooks. Whenever
I
sort the results, or close and open it, all results turn into #Value. The
numbers come back when I reopen the workbook being referenced, but really,
there must be something we can do?

Here's one of those formulae:
=COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma
2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error Checking
underlines everything, then just E5...so there.

Also, is there a way to bypass the Update Links screen if the file hasn't
been moved or renamed?

I thank you for your time.

Arlen

 

[T. Valko]

Let's look at a simple example:

...........A..........
1........x...........
2.....................
3........x...........
4........z...........
5........y...........

We need the count of "x".

=SUMPRODUCT(--(A1:A5="x"))

This expression will return an array of either TRUE or FALSE: (A1:A5="x")

A1 = x = TRUE
A2 = x = FALSE
A3 = x = TRUE
A4 = x = FALSE
A5 = x = FALSE

The end result of our formula is a sum but SUMPRODUCT can't sum those
logical values. So, we need to convert them to numbers somehow. One way to
do this is to use the double unary "--". This will coerce TRUE to 1 and
FALSE to 0. So now our array of TRUE/FALSE is an array of 1/0:

--(A1=x) = 1
--(A2=x) = 0
--(A3=x) = 1
--(A4=x) = 0
--(A5=x) = 0

Then, SUMPRODUCT justs sums up the array:

=SUMPRODUCT({1;0;1;0;0})

Result = 2

The SUMIF alternative works pretty much the same way except that when there
are more than a single array (as in the above example) all the arrays are
multiplied together to arrive at the result.

Using the same sample from above:

...........A..........B
1........x...........5
2.....................2
3........x...........3
4........z...........1
5........y...........6

SUMIF column A = x:

=SUMPRODUCT(--(A1:A5="x"),B1:B5)

We still have our array of 1/0 with --(A1:A5="x") but now we introduced a
2nd array, column B, and these are the values we want to sum. Since the
values in column B are already numeric numbers we don't need to "mess" with
them. So, as I noted, when there is more than one array all the arrays get
multiplied together like this:

1*5 = 5
0*2 = 0
1*3 = 3
0*1 = 0
0*6 = 0

SUMIF A = x, result = 8

See this for a comprehensive analysis of SUMPRODUCT:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP

Biff,

Perfect! But could you explain what is happening, because I'm gonna need
to
use it again with SUMIF and such.

Thanks.

COUNTIF won't work when referencing a closed file. Use SUMPRODUCT.

Have the source file open when you write the formula and then just use
your
mouse to point to the source. This way Excel will put all that path junk
in
for you. Much easier than typing it!

=SUMPRODUCT(--(really_long_pathC1:C100=E5))

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP

I have a workbook with a formulas referencing 5 other workbooks.
Whenever
I
sort the results, or close and open it, all results turn into #Value.
The
numbers come back when I reopen the workbook being referenced, but
really,
there must be something we can do?

Here's one of those formulae:
=COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma
2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error Checking
underlines everything, then just E5...so there.

Also, is there a way to bypass the Update Links screen if the file
hasn't
been moved or renamed?

I thank you for your time.

Arlen

 

[Arlen]

Awesome!

Thank you for taking the time to explain that, Biff.

Have a great day.

Arlen

Let's look at a simple example:

...........A..........
1........x...........
2.....................
3........x...........
4........z...........
5........y...........

We need the count of "x".

=SUMPRODUCT(--(A1:A5="x"))

This expression will return an array of either TRUE or FALSE: (A1:A5="x")

A1 = x = TRUE
A2 = x = FALSE
A3 = x = TRUE
A4 = x = FALSE
A5 = x = FALSE

The end result of our formula is a sum but SUMPRODUCT can't sum those
logical values. So, we need to convert them to numbers somehow. One way to
do this is to use the double unary "--". This will coerce TRUE to 1 and
FALSE to 0. So now our array of TRUE/FALSE is an array of 1/0:

--(A1=x) = 1
--(A2=x) = 0
--(A3=x) = 1
--(A4=x) = 0
--(A5=x) = 0

Then, SUMPRODUCT justs sums up the array:

=SUMPRODUCT({1;0;1;0;0})

Result = 2

The SUMIF alternative works pretty much the same way except that when there
are more than a single array (as in the above example) all the arrays are
multiplied together to arrive at the result.

Using the same sample from above:

...........A..........B
1........x...........5
2.....................2
3........x...........3
4........z...........1
5........y...........6

SUMIF column A = x:

=SUMPRODUCT(--(A1:A5="x"),B1:B5)

We still have our array of 1/0 with --(A1:A5="x") but now we introduced a
2nd array, column B, and these are the values we want to sum. Since the
values in column B are already numeric numbers we don't need to "mess" with
them. So, as I noted, when there is more than one array all the arrays get
multiplied together like this:

1*5 = 5
0*2 = 0
1*3 = 3
0*1 = 0
0*6 = 0

SUMIF A = x, result = 8

See this for a comprehensive analysis of SUMPRODUCT:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP

Biff,

Perfect! But could you explain what is happening, because I'm gonna need
to
use it again with SUMIF and such.

Thanks.

COUNTIF won't work when referencing a closed file. Use SUMPRODUCT.

Have the source file open when you write the formula and then just use
your
mouse to point to the source. This way Excel will put all that path junk
in
for you. Much easier than typing it!

=SUMPRODUCT(--(really_long_pathC1:C100=E5))

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP


I have a workbook with a formulas referencing 5 other workbooks.
Whenever
I
sort the results, or close and open it, all results turn into #Value.
The
numbers come back when I reopen the workbook being referenced, but
really,
there must be something we can do?

Here's one of those formulae:
=COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma
2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error Checking
underlines everything, then just E5...so there.

Also, is there a way to bypass the Update Links screen if the file
hasn't
been moved or renamed?

I thank you for your time.

Arlen

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Awesome!

Thank you for taking the time to explain that, Biff.

Have a great day.

Arlen

Let's look at a simple example:

...........A..........
1........x...........
2.....................
3........x...........
4........z...........
5........y...........

We need the count of "x".

=SUMPRODUCT(--(A1:A5="x"))

This expression will return an array of either TRUE or FALSE: (A1:A5="x")

A1 = x = TRUE
A2 = x = FALSE
A3 = x = TRUE
A4 = x = FALSE
A5 = x = FALSE

The end result of our formula is a sum but SUMPRODUCT can't sum those
logical values. So, we need to convert them to numbers somehow. One way
to
do this is to use the double unary "--". This will coerce TRUE to 1 and
FALSE to 0. So now our array of TRUE/FALSE is an array of 1/0:

--(A1=x) = 1
--(A2=x) = 0
--(A3=x) = 1
--(A4=x) = 0
--(A5=x) = 0

Then, SUMPRODUCT justs sums up the array:

=SUMPRODUCT({1;0;1;0;0})

Result = 2

The SUMIF alternative works pretty much the same way except that when
there
are more than a single array (as in the above example) all the arrays are
multiplied together to arrive at the result.

Using the same sample from above:

...........A..........B
1........x...........5
2.....................2
3........x...........3
4........z...........1
5........y...........6

SUMIF column A = x:

=SUMPRODUCT(--(A1:A5="x"),B1:B5)

We still have our array of 1/0 with --(A1:A5="x") but now we introduced a
2nd array, column B, and these are the values we want to sum. Since the
values in column B are already numeric numbers we don't need to "mess"
with
them. So, as I noted, when there is more than one array all the arrays
get
multiplied together like this:

1*5 = 5
0*2 = 0
1*3 = 3
0*1 = 0
0*6 = 0

SUMIF A = x, result = 8

See this for a comprehensive analysis of SUMPRODUCT:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP

Biff,

Perfect! But could you explain what is happening, because I'm gonna
need
to
use it again with SUMIF and such.

Thanks.



:

COUNTIF won't work when referencing a closed file. Use SUMPRODUCT.

Have the source file open when you write the formula and then just use
your
mouse to point to the source. This way Excel will put all that path
junk
in
for you. Much easier than typing it!

=SUMPRODUCT(--(really_long_pathC1:C100=E5))

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP


I have a workbook with a formulas referencing 5 other workbooks.
Whenever
I
sort the results, or close and open it, all results turn into
#Value.
The
numbers come back when I reopen the workbook being referenced, but
really,
there must be something we can do?

Here's one of those formulae:
=COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma
2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error
Checking
underlines everything, then just E5...so there.

Also, is there a way to bypass the Update Links screen if the file
hasn't
been moved or renamed?

I thank you for your time.

Arlen