Retrieve the data based on string

Question

Hi,

I am building a string from various constants and variables. This string I am evaluating using the named variable and also by VBA function. But none of them worked. One gives me #REF error and the other #VALUE error.

I built the string like this =AC20&AC18 in AC24 cell and the output is like this 'G:CrushDaily plant reports2019[05-2019.xls]C vol'!$F$35

In the name manager, I created a function like this =EVALUATE('C vol'!$AC$24) which I named as Eval

In another cell, I called Eval and it displays #REF error.

I wrote a function in VBA like this
Public Function myEvaluate(aString) As Variant
On Error Resume Next
Application.Volatile
myEvaluate = Evaluate(aString)
On Error GoTo 0
End Function

In another cell, I called this function as = myEvaluate(AC24) which gives me #VALUE error.

So no idea where is the mistake in syntax.