Formulas / changing depending on cell values

[shahzadbux]

Hi,

I have a formula....
(C:csv[2105REG.csv]2105REG'!$C$5) which copys data from a file which is date stamped, can anyone suggest how i can make this formula so that the file name changes to whatever is in cell L3?

here is what i tryed...

=C:csv["L3"]2105REG'!$C$5

BUT IT DID NOT WORK....PLEASE HELP!!!

[IML]

You could use
=INDIRECT("'"&L3&"'!$C$5")

Indirect requires the file name in L3 to be open, otherwise you'll get a ref! error.

[shahzadbux]

Thanks alot,

this will save 150 people about one and a half hours every night!!!!

[shahzadbux]

Just to take this one step further and make it completely idiot-proof...

From what i understand the INDIRECT function will only work if the target file is open, when closed you'll get a #REF! error. Is there any way to make it work when the target file is closed?


All suggestions will be greatly appreciated.

[sen_edp]

Hello

indirect works only with open files.

check this out

http://j-walk.com/ss/excel/tips/tip82.htm
it might help you

[shahzadbux]

I have copied the code below.
How can i get the file name from a particular cell on a worksheet?

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) <> "" Then path = path & ""
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

Lastly,

Is there any chance anyone can explaine the code so i can make it work!!! - I'm not sure which elements i need to change.

Once again Thank You Very much in Advance.