embedding text in a function

[[email protected]]

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

[LitVilkas]

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


[email protected] 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

[[email protected]]

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

[LitVilkas]

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?