embedding text in a function

Use the Indirect function, which allows you to constract formula with
text input, i.e.
=AVERAGE(LN(indirect(Setup!$A$1&":\SampleScenarios\[EuropeanBond.xls]EuropeanBond!$H$6:$CI$1005")))

Since my drive are obviously differently arranged I can't try this
function. Important is, that you remember the apostrophy before your
drive, as in 'D:\SampleScenarios\...

Either make the user include it, or arrange the formula accordingly, as
in
=AVERAGE(LN(indirect("'"&Setup!$A$1&":\SampleScenarios\...

God luck


hermanko@gmail.com wrote:
> Hi,
>
> I am trying to figure out the proper syntax so that i can type in a
> function into a cell that looks like this:
>
> =AVERAGE(LN('D:\Sample
> Scenarios\[EuropeanBond.xls]EuropeanBond'!$H$6:$CI$1005))
>
> The tricky part is that the drive letter will change depending on where
> the file is being read since it will be on a CD, while the entire
> string is pointing to another file on the CD.
>
> let's say i have the user input the drive letter in a cell on a
> different sheet at Setup!$A$1 how would i properly insert the
> single/double quotation marks to make this formula work? I have tried a
> bunch of ways but I keep getting the REF or VALUE errors. Here is an
> example of what i've tried:
>
> =AVERAGE(LN(Setup!$A$1&":\Sample
> Scenarios\[EuropeanBond.xls]EuropeanBond!$H$6:$CI$1005"))
>
> Any help would be greatly appreciated!
> Herman

Hi, thanks for responding...
But your method is not working for me... Let's say in Setup!$A$1, the
user types in "D" (no quotes) for the drive, i still can't get the
formula to properly display the result.
Herman

There could be several reasons for that:
- Your external file needs to be open for a result to be displayed.
- Your range is rather large - are you sure that there are only proper
integer values in that range, and not some text or error values?