Good Morning,

I have several different workbooks that contain monthly information. I am
trying to create file that shows a recap of the 12 months on one seperate
workbook. I have been using indirect( ) references in order to cut down on
my data entry. The problem that I have run into, is that the data will not
display unless the individual workbooks are open. Is there anyway around
this problem? I have thought about using access but it is not feasbile as I
have multiple users entering data into these sheets from different locations.

The INDIRECT function does not work when it references external workbooks,
unless that external workbook is open.

Post the formula you are using and perhaps someone can suggest an
alternative formula that works if the external workbook is closed.

Dave
--
Brevity is the soul of wit.


"PerplexedinKY" wrote:

Good Morning,

I have several different workbooks that contain monthly information. I am
trying to create file that shows a recap of the 12 months on one seperate
workbook. I have been using indirect( ) references in order to cut down on
my data entry. The problem that I have run into, is that the data will not
display unless the individual workbooks are open. Is there anyway around
this problem? I have thought about using access but it is not feasbile as I
have multiple users entering data into these sheets from different locations.

Hi PerplexedinKY

You can make formula links to the cells with a macro
http://www.rondebruin.nl/summary2.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"PerplexedinKY" wrote in message
...
Good Morning,

I have several different workbooks that contain monthly information. I am
trying to create file that shows a recap of the 12 months on one seperate
workbook. I have been using indirect( ) references in order to cut down on
my data entry. The problem that I have run into, is that the data will not
display unless the individual workbooks are open. Is there anyway around
this problem? I have thought about using access but it is not feasbile as I
have multiple users entering data into these sheets from different locations.

The formula I am using is as follows:

=indirect(concatenate($j$2,k2))

where
j2 = '[PDSJ JAN CAL.xls]JAN'! k2 = a3
j3 = '[PDSJ FEB CAL.xls]FEB'! k3 = a4
and so on...

I did it this way because the cells in the monthly sheets jump around based
on days of the week ie a calendar layout.

Any thoughts? Thanks for the help.







"Dave F" wrote:

The INDIRECT function does not work when it references external workbooks,
unless that external workbook is open.

Post the formula you are using and perhaps someone can suggest an
alternative formula that works if the external workbook is closed.

Dave
--
Brevity is the soul of wit.


"PerplexedinKY" wrote:

Good Morning,

I have several different workbooks that contain monthly information. I am
trying to create file that shows a recap of the 12 months on one seperate
workbook. I have been using indirect( ) references in order to cut down on
my data entry. The problem that I have run into, is that the data will not
display unless the individual workbooks are open. Is there anyway around
this problem? I have thought about using access but it is not feasbile as I
have multiple users entering data into these sheets from different locations.