INDIRECT does not seem to work

[Jim Guess]

Here is the situation.

I have a number of Excel spreadsheets stored with a date
format such as: 'title yy mm dd.xls'. They are all
identical in layout. I want to create a spreadsheet that
will find these files and will display the data from a
particular field. So, the reference spreadsheet is built
like this:

Column A Column B Column C
Date Data extracted Formula column
(date entered) =indirect(Cx) formula explained
below.
(date above +7) =indirect(Cx) formula explained
below.
etc.


OK. The formula takes the date from Column A and, using
Concatenate(), Right(), Year(), Month(), Day, and text,
creates a string that looks like this:
'F:\Jim\Anderson\[time sheet 03 08 09.xls]Time'!$H$16
This is clearly displayed in column C except the dates are
changed to match the dates in column A.

Then the Column B formula does INDIRECT(Cx) where x is the
row.

What bugs me is this: I get #REF! in column B for ALL the
INDIRECT() formulas. But I can do the following and it
will work just fine:

1) Open the spreadsheet in question. Select the cell and
Copy.
2) Go to column D in this spreadsheet and do a Paste
Link.
3) The data appears exactly as I wish.
4) I then click on the row where I did the Paste Link and
the link is displayed in the formula bar. It is EXACTLY
like the text in column C!

What am I doing wrong??? Why will it not extract the data
from the spreadsheet?

Thanks!

Jim


PS. I have to get off the internet. I will check back
for an answer, or if someone wants to email me the answer
I will be much obliged!

J

 

[Vasant Nanavati]

INDIRECT does not work with closed workbooks.

 

[Harlan Grove]

INDIRECT does not work with closed workbooks.

...

So see

http://www.google.com/[email protected]

 

[Jim Guess]

-----Original Message-----
...
...

So see

http://www.google.com/groups?selm=hkQVb.2432$_4.259% 40www.newsranger.com

Thanks for the input!

Jim