#REF! error 1

[hellohello1]

I'm using Office 2003.

I have 2 Excel spreadsheets located on a Sharepoint site:
TEST_Master.xls
TEST_Data Owner-HA.xls

The TEST_Master.xls has a few columns which contain links to the other spreadsheet. An example of a cell in TEST_Master is:

=(INDEX('

http://op.ds.com/sites/DocumentLibrary1/

[TEST_Data Owner-HA.xls]Measures-HA'!$C$6:'

http://op.ds.com/sites/DocumentLibrary1/

[TEST_Data Owner-HA.xls]Measures-HA'!$P$6,MONTH(TODAY())+1))

That formula is saying: in the TEST_Data Owner-HA.xls spreadsheet, return the value of the third column in the range of C55.

Everything is fine with the formula, except I get a #REF! message in the TEST_Master.xls. The only way to avoid the #REF! message is if I also have the TEST_Data Owner-HA.xls spreadsheet open. If both spreadsheets are open, then everything works great. But if I close the TEST_Data Owner spreadsheet, the #REF! comes back in the Master spreadsheet.

I have other cells in the Master spreadsheet that are linked to TEST_Data Owner-HA.xls and they work fine, even if the TEST_Data Owner-HA is not open.
Ex:
='

http://op.ds.com/sites/DocumentLibrary1/

[TEST_Data Owner-HA.xls]Measures-HA'!$R$5.

This works fine.

Any ideas why my first link gives the #REF! error? Is it because of the Index function contained within the link?

Thanks,

 

[xlbo]

yup - some functions only work when the other workbook is open - INDEX is one of them

work arounds:

Save the workbook with calculation set to manual so that values are not attempted to be updated unless explicitly told to be

Use some code to overwite formulae with the static values and on button click, re-populate when you need to recalc (could also open the other workbook at the same time to ensure the formulae work)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[hellohello1]

Geoff,

thanks for writing. i have been racking my brain trying to figure out why the #REF! errors were there. So now i know.

I think I'll use HLOOKUP instead of INDEX for my linking.

Thanks!