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!

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
>
>

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\Management 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.

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\Management
> 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
>
>

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" <Brokovich.2cdpie_1155305111.3109@excelforum-nospam.com> wrote
in message news:Brokovich.2cdpie_1155305111.3109@excelforum-nospam.com...

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\Management
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

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!

Thanks for the feed-back.
--

Regards,

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

"Brokovich" <Brokovich.2cdrtq_1155308108.6643@excelforum-nospam.com> wrote
in message news:Brokovich.2cdrtq_1155308108.6643@excelforum-nospam.com...

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