Reading from different files

[KVW]

I am currently working on a excel file which contains information from all different locations on the computer.
Is it possible that exile read the information, without opening the other excel files?

[6StringJazzer]

Yes, depending what what "read the information" means. You need to provide much more information. Do you just need to refer to cells in the other Excel files, or do something more complicated? A simple reference to a cell in another file is

Please Login or Register  to view this content.

[KVW]

The excel file that read the information from the other file is always open.
The sheet must change when somebody else change something in another excel file that is connected to the first file

[6StringJazzer]

I'm sorry but I just have to say that it's "Excel", not "exile".

Are you just doing a straight reference of other data? If so then I have already provided the solution.

Are you doing something more interesting, like a lookup based on data in the other file? Then the solution is very similar, you just include the file name and sheet name in the cell reference, like in my simpler example.

This gets more difficult if the files that you want to reference change dynamically. That is, if today you want to reference "May 2014.xlsx" and next month you want that reference to automatically change to "June 2014.xlsx" then we are going to need a bigger thread.

[KVW]

My problem is that Excel only show the charts on my file if the source is open.

this are my code:
=COUNTIFS(
'Location]Name'!$J$3:$J$999,
">="&TODAY()-30,'Location'!$J$3:$J$999,"<="&TODAY(),
'Location]Other'!$N$3:$N$999,"Name")

&

=SUMIFS(
'Location]Other'!$O$3:$O$999,
'Location'!$J$3:$J$999,">="&TODAY()-30,
'Location]Other'!$J$3:$J$999,"<="&TODAY(),
'Location]Other'!$N$3:$N$999,"name")

Both codes work perfectly with the file open, but when the source is not open it is giving ##### and #VALUE!

[6StringJazzer]

COUNTIFS and SUMIFS require the other file to be open. One way to solve this is with macros. A more straightforward way is to use a "shadow" worksheet. The shadow worksheet has a formula in every cell that starts in A1 with

=A1

and so forth with every cell that is used in the other file.

Then your formulas refer to the "shadow" sheet instead of the other file.