embedding text in a function

H

hermanko

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
 
L

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
 
H

hermanko

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
 
L

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?
 
Top