Reference a file by concatenating cell variables

[Michael]

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!

 

[TomPl]

This might be what you are looking for:

=CONTATENATE("='C:\ConsolPeriod Report",TEXT(A3,"mm/dd/yy"),".xls' " & SData)

 

[Sheeloo]

If your string is evaluated to the right result then just put an INDIRECT
around it...

For example if A1 has the string Sheet2!A1
=INDIRECT(A1) will evaluate to =Sheet2!A1 and give you the value in Sheet 2 A1

 

[TomPl]

Spelling - Change Contatenate to Concatenate.

 

[Pete_UK]

Note that INDIRECT only works with open files. If you want to work
with closed files (which is implied by your use of the path) then you
will need to get the free add-in morefunc and use INDIRECT.EXT.

Note also that you need square brackets around the filename, and the
TEXT function in Tom's reply should probably be:

TEXT(A3,"m-d-yy")

Also, there is a ! missing in the formula.

Hope this helps.

Pete

 

[Dave Peterson]

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]

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

 

[Peo Sjoblom]

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]

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

 

[Dave Peterson]

Watch how you build your formula.

This formula isn't correct:
='F:\Documents\Daily Report 10-7-08.xls'SPallets

Try:
='F:\Documents\[Daily Report 10-7-08.xls]'SPallets
(notice the []'s)

Once you get that working, try:

=indirect.ext("'F:\Documents\[Daily Report "
& text(a3,"m-d-yy") & ".xls]'SPallets")

and watch your typing. It's not indirect.exe

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]

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

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

 

[Michael]

Peo,

I don't know how to thank you but it worked! I was trying different
combinations of your suggestions and this one finally pulled the data:

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

This one only displayed the file name:

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

and this one showed "#NAME?" in the cell"

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

I thought I was copying everything that you suggested but I must have
entered something wrong.

Thank you for your help!

Mike

 

[Michael]

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]

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