Sharing data between workbooks

Hi

I'm using shared workbooks for separate 'Regions' to work out queries organised in tables. Unfortunately each workbook must be a separate file because of network restrictions (eg can't have each region on a separate worksheet within one workbook).

When trying to create a summary workbook, I would like it to count the amount of 'Non-resolved' queries in a certain column in each of the other workbooks. Using the COUNTIF function, I can get Excel to look in the other workbooks and retrieve the data.

However, when I close the worksheet and re-open it, it now returns #VALUE in each of the boxes. I don't understand how it can work when I configure it, but upon saving it goes mad.

Formula before quitting (works):

=COUNTIF('Anglian monthly queries.xls'!FAO_column,B2)

Formula after quitting (doesn't work)

=COUNTIF('O:\HR User Manuals\Monthly query interface\Anglian monthly queries.xls'!FAO_column,B2)

It is referring to the same file both times, but for some reason once it changes it can't find the values.

Sorry if the explanation is a bit long-winded. It seems to me that this is something Excel should do easily. I'd really appreciate it if anyone could suggest a reason as to what is going on!

Many thanks and kind regards

Nick

Hi,

Are you working with closed files or opened files ...?

Carim

When I set the formula the files are open. It goes wrong when everything is closed and then I open the summary sheet.

Cheers

Nick

Sorted (i think). Used external data query. Is a bit long-winded but it seems to have worked.

Cheers

Nick