Reference a file by concatenating cell variables
 

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!

Reference a file by concatenating cell variables
 

This might be what you are looking for:

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

Reference a file by concatenating cell variables
 

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

"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!

Reference a file by concatenating cell variables
 

Spelling - Change Contatenate to Concatenate.

"TomPl" wrote:

This might be what you are looking for:

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

Reference a file by concatenating cell variables
 

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

On Oct 22, 4:14*pm, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote:
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



"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!- Hide quoted text -

- Show quoted text -

Reference a file by concatenating cell variables
 

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

Reference a file by concatenating cell variables
 

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

Reference a file by concatenating cell variables
 

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

Reference a file by concatenating cell variables
 

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

Reference a file by concatenating cell variables
 

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



Michael wrote:

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





--

Dave Peterson

Reference a file by concatenating cell variables
 

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

Reference a file by concatenating cell variables
 

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



"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

Reference a file by concatenating cell variables
 

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

Reference a file by concatenating cell variables
 

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