Ref a file in date format

[Microsoft Communities]

I would like to reference a file in a formula using a date format.
The file would be called "JulSales" the next month it would be called
"AugSales". I would like to do this in a formula that ha a date in cell B1.
The date in B1 would be "05-08-09" dd-mm-yy format.

 

[Pete_UK]

Use this formula:

=TEXT(B1,"mmm")&"Sales"

or you might want to make it:

=TEXT(B1,"mmm")&"Sales.xls"

If by "reference" you mean that you would like to bring some data from
it, then the normal way of doing that would be to use the INDIRECT
function to build up a text string representing the cell address.
However, this will not work with closed workbooks.

Hope this helps.

Pete

 

[Rick Rothstein]

We can even eliminate the concatenation like this..

=TEXT(B1,"mmm""Sales""")

or this slightly more obfuscated way...

=TEXT(B1,"mmm\Sal\e\s")

--
Rick (MVP - Excel)


Use this formula:

=TEXT(B1,"mmm")&"Sales"

or you might want to make it:

=TEXT(B1,"mmm")&"Sales.xls"

If by "reference" you mean that you would like to bring some data from
it, then the normal way of doing that would be to use the INDIRECT
function to build up a text string representing the cell address.
However, this will not work with closed workbooks.

Hope this helps.

Pete