Formula Links to seperate workbooks
 

Hi there

I am currently working on a spreadsheet which conatins formulas that
link to numerous other spreadsheets.

The problem I am having is that when you open the new spreasdheet, in
the cells which contain formulas linking to the other spreadsheets they
display #VALUE! unless you actually open the source spreadsheets.

I want the formulas in the cells to update without the user having to
open the source spreadsheet, otherwise there will be no point in having
the new spreadsheet! I don't want the users to have to open numerous
spreadsheets to view the information they need.

One thing I have noticed is that cells that link to another spreadsheet
(but do not incorporate formulas) are working fine, although you do have
to enter the password needed to access that particular source
spreadsheet.

Can anyone help with this?

Thanks!


--
Brokovich
------------------------------------------------------------------------
Brokovich's Profile: http://www.excelforum.com/member.php...o&userid=37098
View this thread: http://www.excelforum.com/showthread...hreadid=570724

Formula Links to seperate workbooks
 

Hi--

What do you mean by cells that refer to other workbooks, but which are not
in formulas, work fine? Any cell which references another workbook is, by
definition, a formula.

In any event, have your users go to Tools--Options--Calculation, and click
Update remote references and save external link values. See if that solves
the problem.

"Brokovich" wrote:


Hi there

I am currently working on a spreadsheet which conatins formulas that
link to numerous other spreadsheets.

The problem I am having is that when you open the new spreasdheet, in
the cells which contain formulas linking to the other spreadsheets they
display #VALUE! unless you actually open the source spreadsheets.

I want the formulas in the cells to update without the user having to
open the source spreadsheet, otherwise there will be no point in having
the new spreadsheet! I don't want the users to have to open numerous
spreadsheets to view the information they need.

One thing I have noticed is that cells that link to another spreadsheet
(but do not incorporate formulas) are working fine, although you do have
to enter the password needed to access that particular source
spreadsheet.

Can anyone help with this?

Thanks!


--
Brokovich
------------------------------------------------------------------------
Brokovich's Profile: http://www.excelforum.com/member.php...o&userid=37098
View this thread: http://www.excelforum.com/showthread...hreadid=570724

Formula Links to seperate workbooks
 

Hi Dave

Thanks for that info. I tried it but these options were already ticked.


What I meant by cells without formulas was as below:

Cells which contain the following
='[MI Master Spreadsheet 0607.xls]Adam Fearn'!$H$74
are updating without problem, whereas the cells containing the below
=SUMIF('K:\Financial_practitioners\Management Info\Expenses\[Peter
Aylward
0607.xls]Jun'!$C$6:$C$29,"AF",'K:\Financial_practitioners\M anagement
Info\Expenses\[Peter Aylward 0607.xls]Jun'!$S$6:$S$29)
are not.

I thought this could be due to the fact that the referenced worksheets
are within a formula...?

Can you help any further?

Thanks again.


--
Brokovich
------------------------------------------------------------------------
Brokovich's Profile: http://www.excelforum.com/member.php...o&userid=37098
View this thread: http://www.excelforum.com/showthread...hreadid=570724

Formula Links to seperate workbooks
 

Ok, now your question makes more sense. You'd have to insure that the copy
of the workbook your users are using has the same filepaths as you
do--otherwise, the links will be broken.

How do you distribute the workbook to your users? Do you email it to them
or email them the filepath to access it?

If they are all on the same network as you, it would be better to place the
workbook in the same folder as the workbooks to which it links, and instruct
your users to access the workbook there, and, most importantly, not to save
the workbook to a different directory.

"Brokovich" wrote:


Hi Dave

Thanks for that info. I tried it but these options were already ticked.


What I meant by cells without formulas was as below:

Cells which contain the following
='[MI Master Spreadsheet 0607.xls]Adam Fearn'!$H$74
are updating without problem, whereas the cells containing the below
=SUMIF('K:\Financial_practitioners\Management Info\Expenses\[Peter
Aylward
0607.xls]Jun'!$C$6:$C$29,"AF",'K:\Financial_practitioners\M anagement
Info\Expenses\[Peter Aylward 0607.xls]Jun'!$S$6:$S$29)
are not.

I thought this could be due to the fact that the referenced worksheets
are within a formula...?

Can you help any further?

Thanks again.


--
Brokovich
------------------------------------------------------------------------
Brokovich's Profile: http://www.excelforum.com/member.php...o&userid=37098
View this thread: http://www.excelforum.com/showthread...hreadid=570724

Formula Links to seperate workbooks
 

The fact is that some functions *do not* work on *closed* WBs.
Sumif, Countif, Index are some of these.

A work-around for your formula is to use an *array* formula combination of
Sum and If:

=SUM(IF('K:\Financial_practitioners\Management
Info\Expenses\[PeterAylward0607.xls]Jun'!$C$6:$C$29="AF",'K:\Financial_pract
itioners\Management Info\Expenses\[Peter Aylward 0607.xls]Jun'!$S$6:$S$29))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

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

"Brokovich" wrote
in message ...

Hi Dave

Thanks for that info. I tried it but these options were already ticked.


What I meant by cells without formulas was as below:

Cells which contain the following
='[MI Master Spreadsheet 0607.xls]Adam Fearn'!$H$74
are updating without problem, whereas the cells containing the below
=SUMIF('K:\Financial_practitioners\Management Info\Expenses\[Peter
Aylward
0607.xls]Jun'!$C$6:$C$29,"AF",'K:\Financial_practitioners\M anagement
Info\Expenses\[Peter Aylward 0607.xls]Jun'!$S$6:$S$29)
are not.

I thought this could be due to the fact that the referenced worksheets
are within a formula...?

Can you help any further?

Thanks again.


--
Brokovich
------------------------------------------------------------------------
Brokovich's Profile:
http://www.excelforum.com/member.php...o&userid=37098
View this thread: http://www.excelforum.com/showthread...hreadid=570724

Formula Links to seperate workbooks
 

Thankyou both very much for your help on this one. To be honest I found
the easiest way around it was to place the sumif function on the source
workbook and then link to that result.

This has worked perfectly so I will stick with that as it is a lot
simpler and means I don't have to move any of the source workbook
around.

Thanks again though!


--
Brokovich
------------------------------------------------------------------------
Brokovich's Profile: http://www.excelforum.com/member.php...o&userid=37098
View this thread: http://www.excelforum.com/showthread...hreadid=570724

Formula Links to seperate workbooks
 

Thanks for the feed-back.
--

Regards,

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

"Brokovich" wrote
in message ...

Thankyou both very much for your help on this one. To be honest I found
the easiest way around it was to place the sumif function on the source
workbook and then link to that result.

This has worked perfectly so I will stick with that as it is a lot
simpler and means I don't have to move any of the source workbook
around.

Thanks again though!


--
Brokovich
------------------------------------------------------------------------
Brokovich's Profile:
http://www.excelforum.com/member.php...o&userid=37098
View this thread: http://www.excelforum.com/showthread...hreadid=570724