Links showing #VALUE!

I have an EXCEL file (named 'Master') with 2 worksheets.

On each of these worksheets I have formulae which links with data on 2
different EXCEL files (named 'Data 1' & 'Data 2'). These 2 files only
hold tables containing data inputs (i.e. no formulae).

When I open the 'Master' file it asks if I want to refresh the Links,
which I do. When this is completed one worksheet in the 'Master' file
shows what I expect it too, having been linked to 'Data 1' file.
However, the other worksheet shows #VALUE! in all the cells that
relate to a link in the 'Data 2' file. If I then open 'Data 2' file
and then immediately close it, then look at the problematic worksheet
in the 'Master' file, everything has been correctly populated with the
figures from the 'Data 2' file.

I'm using EXCEL 2000.

What is the problem on this? I wrote the formulae with all files Open,
then Saved & Closed all files, then opened the 'Master' file only to
discover this problem.

[G]rumpy [O]ld [D]uffer wrote:
What is the problem on this?


Sometimes link formulas are just too complicated for Excel to compute
without fully opening the source file. It always has to read the source
file of course, but if the formula is complicated (e.g. using VLOOKUP
in a big table) it may be too much of a challenge.

The workaround is to open the source file, as you have discovered.

Out of interest, what is an example formula that gives the #VALUE
error?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Out of interest, what is an example formula that gives the #VALUE
error?

Bill,
Thanks for the reply.

An example of the formulae is :

[This is the formulae returning #Value!]
SUMIF('C:\Documents and Settings\My Documents\OP''s Development
Manager\Forecast Process\2004_05 Forecast Process\[Depot
Numbers(Planned).xls]Planned
Traffic'!$B$4:$B$3504,F$6&"/"&$B18,'C:\Documents and Settings\My
Documents\OP''s Development Manager\Forecast Process\2004_05 Forecast
Process\[Depot Numbers(Planned).xls]Planned Traffic'!$E$4:$E$3504)

[This formulae (on another worksheet) is working fine]
IF(Depot_Number=1,SUMIF(Data!$G$68:$G$121,$C14,Dat a!I$68:I$121),SUMIF('C:\Documents
and Settings\My Documents\OP''s Development Manager\Forecast
Process\2004_05 Forecast Process\[Depot Numbers(1st).xls]Depot
Numbers'!$B$4:$B$3504,$B14&"/"&'Depot Numbers'!$C$4,'C:\Documents and
Settings\My Documents\OP''s Development Manager\Forecast
Process\2004_05 Forecast Process\[Depot Numbers(1st).xls]Depot
Numbers'!C$4:C$3504))

Regards,
Ian D.

Hi
now it's clear :-)
SUMIF won't work on closed workbooks. You ahve to use a different
formula (e.g. SUMPRODUCT). Try:
=SUMPRODUCT(--('C:\Documents and Settings\My Documents\OP''s
Development
Manager\Forecast Process\2004_05 Forecast Process\[Depot
Numbers(Planned).xls]Planned
Traffic'!$B$4:$B$3504=F$6&"/"&$B18),'C:\Documents and Settings\My
Documents\OP''s Development Manager\Forecast Process\2004_05 Forecast
Process\[Depot Numbers(Planned).xls]Planned Traffic'!$E$4:$E$3504)


--
Regards
Frank Kabel
Frankfurt, Germany

"[G]rumpy [O]ld [D]uffer" schrieb im
Newsbeitrag om...
Out of interest, what is an example formula that gives the #VALUE
error?

Bill,
Thanks for the reply.

An example of the formulae is :

[This is the formulae returning #Value!]
SUMIF('C:\Documents and Settings\My Documents\OP''s Development
Manager\Forecast Process\2004_05 Forecast Process\[Depot
Numbers(Planned).xls]Planned
Traffic'!$B$4:$B$3504,F$6&"/"&$B18,'C:\Documents and Settings\My
Documents\OP''s Development Manager\Forecast Process\2004_05 Forecast
Process\[Depot Numbers(Planned).xls]Planned Traffic'!$E$4:$E$3504)

[This formulae (on another worksheet) is working fine]

IF(Depot_Number=1,SUMIF(Data!$G$68:$G$121,$C14,Dat a!I$68:I$121),SUMIF('
C:\Documents
and Settings\My Documents\OP''s Development Manager\Forecast
Process\2004_05 Forecast Process\[Depot Numbers(1st).xls]Depot
Numbers'!$B$4:$B$3504,$B14&"/"&'Depot Numbers'!$C$4,'C:\Documents and
Settings\My Documents\OP''s Development Manager\Forecast
Process\2004_05 Forecast Process\[Depot Numbers(1st).xls]Depot
Numbers'!C$4:C$3504))

Regards,
Ian D.

[G]rumpy [O]ld [D]uffer wrote:
Out of interest, what is an example formula that gives the #VALUE
error?

Interesting.
The formulas are apparently quite similar, aren't they.

I assume that Depot_Number is not 1 in the case of the second formula
(or else it would work fine as it is not accessing the external
workbook).

SUMIF seems quite often to be the culprit when #VALUE errors appear on
external links.

It never ceases to amaze me that such formulas EVER work when the
source workbook is closed. Excel must have to open the file and
construct the worksheet "behind the scenes" in order to be able to
evaluate the formula.

Is there any obvious difference between the content of the tables in
Depot Numbers(1st).xls and in Depot Numbers(Planned).xls? e.g. does
one have (Planned) have formulas where (1st) has constant data?


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Thank you for the SUMPRODUCT tip.

However, I'm getting in a real mess trying to get this function to
work.

The Table that will have the formulae in looks like :-
Wk No Wk No Wk No Wk No
Depot 1 2 3 4
Aberdeen
Aldershot
Belfast
Birmingham

... and I'm using the Depot & Wk No (i.e. F$6&"/"&$B18) to 'lookup' a
unique record in Col. B of a 'Data 1' file (closed) that looks like :-

Column Column Column
Row B C D
4 Lookup Wk No Planned
5 1/ALL 1 712,249
6 1/Aberdeen 1 4,158
7 1/Aberdeen RML 1 2,743
8 1/Aldershot 1 15,508
9 1/Belfast 1 18,905
10 1/Birmingham 1 16,450
This is the table that will have 3,500 rows to look at.

Therefore, in the first table 4,158 will be returned for Aberdeen
under Wk 1, 18,905 will be returned for Belfast under Wk 1 etc. etc.

Can you help me sort out the formulae on this? I don't understand the
'Help' description in EXCEL and I think I need to enter an array where
you have shown '--' in the formulae below, but this is where I'm
getting in a mess.

Regards,
Ian D.



"Frank Kabel" wrote in message ...
Hi
now it's clear :-)
SUMIF won't work on closed workbooks. You ahve to use a different
formula (e.g. SUMPRODUCT). Try:
=SUMPRODUCT(--('C:\Documents and Settings\My Documents\OP''s
Development
Manager\Forecast Process\2004_05 Forecast Process\[Depot
Numbers(Planned).xls]Planned
Traffic'!$B$4:$B$3504=F$6&"/"&$B18),'C:\Documents and Settings\My
Documents\OP''s Development Manager\Forecast Process\2004_05 Forecast
Process\[Depot Numbers(Planned).xls]Planned Traffic'!$E$4:$E$3504)

Hi
no you need the '--' this coerces the boolean values to real numbers.
Just use the formula as posted :-)

--
Regards
Frank Kabel
Frankfurt, Germany

"[G]rumpy [O]ld [D]uffer" schrieb im
Newsbeitrag om...
Thank you for the SUMPRODUCT tip.

However, I'm getting in a real mess trying to get this function to
work.

The Table that will have the formulae in looks like :-
Wk No Wk No Wk No Wk No
Depot 1 2 3 4
Aberdeen
Aldershot
Belfast
Birmingham

.. and I'm using the Depot & Wk No (i.e. F$6&"/"&$B18) to 'lookup' a
unique record in Col. B of a 'Data 1' file (closed) that looks like
:-

Column Column Column
Row B C D
4 Lookup Wk No Planned
5 1/ALL 1 712,249
6 1/Aberdeen 1 4,158
7 1/Aberdeen RML 1 2,743
8 1/Aldershot 1 15,508
9 1/Belfast 1 18,905
10 1/Birmingham 1 16,450
This is the table that will have 3,500 rows to look at.

Therefore, in the first table 4,158 will be returned for Aberdeen
under Wk 1, 18,905 will be returned for Belfast under Wk 1 etc. etc.

Can you help me sort out the formulae on this? I don't understand the
'Help' description in EXCEL and I think I need to enter an array
where
you have shown '--' in the formulae below, but this is where I'm
getting in a mess.

Regards,
Ian D.



"Frank Kabel" wrote in message
...
Hi
now it's clear :-)
SUMIF won't work on closed workbooks. You ahve to use a different
formula (e.g. SUMPRODUCT). Try:
=SUMPRODUCT(--('C:\Documents and Settings\My Documents\OP''s
Development
Manager\Forecast Process\2004_05 Forecast Process\[Depot
Numbers(Planned).xls]Planned
Traffic'!$B$4:$B$3504=F$6&"/"&$B18),'C:\Documents and Settings\My
Documents\OP''s Development Manager\Forecast Process\2004_05
Forecast
Process\[Depot Numbers(Planned).xls]Planned Traffic'!$E$4:$E$3504)