A formula can be 1024 characters long, is it possible
that you hit this limit?

Besides that I don't know what limits
this particular function has. I rarely use these functions
mainly because I don't have any need for them.

--


Regards,


Peo Sjoblom

"Michael" wrote in message
...

Peo,

One final comment. When I transitioned from my test files to my actual
file
references I noticed that there is a limitation as to how many sub
directories the function will navigate. My test case was one and my
actual
files are buried seven levels deep in my file system. I tried to use a
shortcut link as a pointer but that didn't work either. If my files were
only 5 levels deep the syntax worked fine. Perhaps that was my problem
all
along. Do you know of this limitation in pointing to sub directories in a
file system?

Mike


"Peo Sjoblom" wrote:

For one thing remove the equal sign before the F hard drive letter.

Can I assume that this particular file is called

Daily Report 10-7-08.xls

and that it is in a folder called

Documents

?


and SPallets is the name of a single cell?



This works for me


=INDIRECT.EXT("'F:\Documents\Daily Report
"&MONTH(A$3)&"-"&DAY(A$3)&"-"&TEXT(A$3,"yy")&".xls'!SPallets")

note that I don't use CONCATENATE since it makes it harder IMHO



So to display just the string it would look like



="'F:\Documents\Daily Report
"&MONTH(A$3)&"-"&DAY(A$3)&"-"&TEXT(A$3,"yy")&".xls'!SPallets"

Note that I changed one function, I removed the right part in this case
it
might not matter I find that it easier to use the TEXT function

Using RIGHT and YEAR it would look like


=INDIRECT.EXT("'F:\Documents\Daily Report
"&MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2)&".xls'!SPallets")


single string


="'F:\Documents\Daily Report
"&MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2)&".xls'!SPallets"



--


Regards,


Peo Sjoblom



"Michael" wrote in message
...

The actual string is that I am working with is:

=CONCATENATE("='F:\Documents\Daily Report
",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SPallets")

where A$3 = 10/7/08

The above string displays the file name correctly but doesn't retrieve
the
data.

I added the indirect.exe function below to see if that would work and
got
"#REF!" in the cell:

=INDIRECT.EXT("'F:\Documents\Daily Report 10-7-08.xls'SPallets")

Then I added the cell reference to build the file name and got "#NAME?"

=INDIRECT.EXE(CONCATENATE("='F:\Documents\Daily Report
",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SPallets"))

If type in the following into the cell the data is pulled from the file
as
expected:

='F:\Documents\Daily Report 10-7-08.xls'!SPallets

Still beating my head against the wall......


"Peo Sjoblom" wrote:

It works, of course you need to have a correct string
within that function to make it work.

How does your formula that returns an error look?

--


Regards,


Peo Sjoblom

"Michael" wrote in message
...

Dave,

I must really be thick headed on this one. I downloaded and
installed
the
function suite that includes the indirect.exe function. I did as
you
suggested (open the sending file, insert the formula and then close
the
sending file to get the correct path). I tried to use the
indirect.ext
function without attempting to build the file name and all I get is
"#REF!"
in the cell for a result.

I don't really care if the workbook is open or closed (I can open
workbooks
as needed to update the cells). My problem seems to stem from the
building
the file name from a variable string that includes the date of the
sending
workbook. If I type in the file and path the cell value is updated
from
the
referenced workbook. If I build the file name to include the date
from
the
cell above, only the file name is displayed in the cell and the cell
value
is
not pulled from the sending file. As far as I can see the the file
reference
is accurate but it just won't pull the data.

Thanks in advance for your thoughts.

Mike




"Dave Peterson" wrote:

The function you'd want to use that's built into excel is
=indirect().
But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin
(morefunc.xll)
at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

====
ps, you can use this function for the date formatting:

....,text(a$3,"m-d-yy"),...

pps. I find using the & operator easier to type than the
=concatenate()
function:

=concatenate(a1,a2,a3)
becomes
=a1&a2&a3

ppps.

Open the sending workbook and create a formula in the receiving
workbook
that
retrieves the value from that cell.

Then close the sending workbook and you'll see the syntax for the
string
you
want to use in the =indirect.ext() function.

Your existing syntax isn't going to work.

Michael wrote:

I am attempting to concatenate a cell value (date) with a file
name
to
create
a data reference to a value within an Excel file. If I use the
absolute file
name the function works but if I build the file name by
concatenating
the
date the only thing the spreadsheet does is display the file name
an
not
capture the referenced data cell.

The dates are represented in cells A3, A4, A5, A6, etc as 2/2/08,
2/3/08,
2/4/08, 2/5/08, etc.

The formula in cells B3, B4, B5, B6, etc. is:

=CONTATENATE("='C:\ConsolPeriod Report
",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SData")

Where the reports are stored as "Period Report 2-2-08", "Period
Report
2-3-08", "Period Report 2-4-08", etc.

Any suggestions on how to get the formula to grab the data in the
cell
named
"SData" in each of the reports instead of just displaying the
name
of the report?

Thanks for the brain power of the group!

--

Dave Peterson