getting file name from cell

A

andy

Is there anyway possible to get the filename from a cell? I have workbooks
that are all named with a date corresponding to the end of the week. Is there
anyway possible for a cell to look up the date that is used on the current
sheet then take away seven days from that date and use this new date as a
link to a workbook, and bring back values.This would save me alot of trouble
not having to manually put in the previous weeks date.

An example of this is:

=SUM(SUM([C37.xls]'Sausage Butchery (Lower)'!$I:$I)+SUM([C37.xls]'Sausage
Butchery TW (Higher)'!$I:$I)+SUM([C37.xls]'Sausage Butchery
(Higher)'!$I:$I))+SUM([C37.xls]'Sausage Butchery TW (Lower)'!$I:$I)

where C37 would be the cell where the previous weeks date is held, and
therefore the filename
 
D

David McRitchie

Hi Andy,

Try INDIRECT(c37 & ".xls")

different example of use:
=INDIRECT("'C:\temp\[Output" & TEXT(A1,"yyyymmdd") & ".csv]Output" & TEXT(A1,"yyyymmdd") & "'!a3")
 
F

Frank Kabel

Hi David
normally Harlan would point this out :)
in your second example no need for the path information as Excel's
INDIRECT won't work on closed workbooks. so the path information is not
required :)
So the following would be sufficient:
=INDIRECT("'[Output" & TEXT(A1,"yyyymmdd") & ".csv]Output" &
TEXT(A1,"yyyymmdd") & "'!a3")

--
Regards
Frank Kabel
Frankfurt, Germany


David said:
Hi Andy,

Try INDIRECT(c37 & ".xls")

different example of use:
=INDIRECT("'C:\temp\[Output" & TEXT(A1,"yyyymmdd") & ".csv]Output" &
TEXT(A1,"yyyymmdd") & "'!a3")

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

andy said:
Is there anyway possible to get the filename from a cell? I have
workbooks that are all named with a date corresponding to the end of
the week. Is there anyway possible for a cell to look up the date
that is used on the current sheet then take away seven days from
that date and use this new date as a link to a workbook, and bring
back values.This would save me alot of trouble not having to
manually put in the previous weeks date.

An example of this is:

=SUM(SUM([C37.xls]'Sausage Butchery
(Lower)'!$I:$I)+SUM([C37.xls]'Sausage Butchery TW
(Higher)'!$I:$I)+SUM([C37.xls]'Sausage Butchery
(Higher)'!$I:$I))+SUM([C37.xls]'Sausage Butchery TW (Lower)'!$I:$I)

where C37 would be the cell where the previous weeks date is held,
and therefore the filename
Click to expand...
Click to expand...
 
D

David McRitchie

I will never ever understand the concept of reading
from a closed file without opening it <grin>
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Frank Kabel said:
Hi David
normally Harlan would point this out :)
in your second example no need for the path information as Excel's
INDIRECT won't work on closed workbooks. so the path information is not
required :)
So the following would be sufficient:
=INDIRECT("'[Output" & TEXT(A1,"yyyymmdd") & ".csv]Output" &
TEXT(A1,"yyyymmdd") & "'!a3")

--
Regards
Frank Kabel
Frankfurt, Germany


David said:
Hi Andy,

Try INDIRECT(c37 & ".xls")

different example of use:
=INDIRECT("'C:\temp\[Output" & TEXT(A1,"yyyymmdd") & ".csv]Output" &
TEXT(A1,"yyyymmdd") & "'!a3")

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

andy said:
Is there anyway possible to get the filename from a cell? I have
workbooks that are all named with a date corresponding to the end of
the week. Is there anyway possible for a cell to look up the date
that is used on the current sheet then take away seven days from
that date and use this new date as a link to a workbook, and bring
back values.This would save me alot of trouble not having to
manually put in the previous weeks date.

An example of this is:

=SUM(SUM([C37.xls]'Sausage Butchery
(Lower)'!$I:$I)+SUM([C37.xls]'Sausage Butchery TW
(Higher)'!$I:$I)+SUM([C37.xls]'Sausage Butchery
(Higher)'!$I:$I))+SUM([C37.xls]'Sausage Butchery TW (Lower)'!$I:$I)

where C37 would be the cell where the previous weeks date is held,
and therefore the filename
Click to expand...
Click to expand...
Click to expand...
 
Top