J
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
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