I'm looking for a similar solution except with a User Defined function.

I'd like to be able to build a UDF that pulls the value from a closed
workbook
based on user inputs. I can get VBA to determine what data to pull
(i.e build a string that references the data:
"='C:\My Folder\[My File.xls]Sheet1'!A1"),
but I can't figure out how to get the value on a worksheet.

I tried the solution provided on John Walkenbach's site
(' Execute an XLM macro via GetValue = ExecuteExcel4Macro(arg))
http://www.j-walk.com/ss/excel/tips/tip82.htm,
but it does not function in a worksheet formula.

It be nice to be able to enter the following in a cell:
=get_Value(path, file, sheet, cellref)
and get the value returned.

Is there any workaround?

I'd like a User Defined Function solution so that I can call the values
where ever I need them in a worksheet. The macro approach of
creating the string, setting a cell formula to the string, then pasting
the value doesn't allow much flexibility in pulling the data - the
desired data varies to much, plus any updates to the source files
would require the macro to be re-run (not a true link)

Any suggestions?