getting file name from cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi Andy,

Try INDIRECT(c37 & ".xls")

different example of use:
=INDIRECT("'C:\temp\[Output" & TEXT(A1,"yyyymmdd") & ".csv]Output" & TEXT(A1,"yyyymmdd") & "'!a3")
 
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")



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...
 
I will never ever understand the concept of reading
from a closed file without opening it <grin>


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



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