Formula Links to seperate workbooks

[Brokovich]

Hi there

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

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

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

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

Can anyone help with this?

Thanks

 

[Guest]

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]

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\[Pete
Aylwar
0607.xls]Jun'!$C$6:$C$29,"AF",'K:\Financial_practitioners\Managemen
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 worksheet
are within a formula...?

Can you help any further?

Thanks again

 

[Guest]

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.

 

[RagDyeR]

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

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

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

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

Thanks again though

 

[RagDyeR]

Thanks for the feed-back.
--

Regards,

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

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!