Look up a value in a workbook whose name is in a cell in active workbook

I have a small function (in the active workbook A) which looks up a value from another workbook (B).

This is the function.
Public Function Getmydata(wbk, ws, cref)
Dim wb As Workbook
Set wb = Workbooks(wbk)
Getmydata = wb.Worksheets(ws).Range(cref)
End Function

The sheet name to be used in B is dependent on a date in the worksheet in A (sheet names are Month names).
The formula works as required if I use a cell formula such as :
=getmydata("Source.xls",TEXT(G1, "mmmm yyyy"),"A1")
where "source.xls" is the name of the other workbook.
But I do not want to specify the workbook name explicitly in the formula. I want hold the workbook name in a cell (D1) and so say :
=getmydata(D1,TEXT(G1, "mmmm yyyy"),"A1")
where I have entered Source.xls into D1.

But this produces an error #VALUE! in the cells and the information message says 'A value used in the formula is of the wrong datatype'

I have tried a number of things to try to force D1 to be accepted but can't achieve it.
Can anyone say why it doesn't work and/or how to make it work.

----------------------------------------------------------------------------------------------------------------------

(I can get the answer i want if i make the function look at D1 instead of the cell formula looking at D1:
In the cell i use:
=getmydata("D1",TEXT($G$1, "mmmm yyyy"),"A1")

and in the function I do:
Public Function Getmydata(wbk, ws, cref)
Dim wb As Workbook
Set wb = Workbooks(Range(wbk).Value)
Getmydata = wb.Worksheets(ws).Range(cref)
End Function

but surely I should be able somehow to use the cell address in the formula as in my 'non-working' version)










Start Free Trial