Help me in Writing Formula.

[gnaga]

Hi,

I would like to write the following formula in cell B1 of this workbook

=LOOKUP(MATCH(9.999999999999E+307,C:C))

But the data I am looking up is from the other workbook stored in the directory "C:GNaga" and the workbook name is "Stock.xls"

I want to supply the workbook name to the formula from column A of this workbook.
i.e A1 of this workbook is housing "Stock"

In both the workbook my data is is in Sheet1.

Can anyone help for me? No VBA please.

Thanks

GNaga

 

[gnaga]

Yes Aladin It is working fine.

I am using single only. If I use more than one then I am getting a error of #REF.

It takes around 3 to 5 seconds to return the value.

Thanks a lot.

GNaga

 

[gnaga]

Aladin I have tested your formula in my office machine it was working alright. But when I try the same thing in my home it does not work. I am getting an error of #REF

I need to open the source file then the error goes off. Both machines are running with Office XP.

Thanks

GNaga

 

[Aladin Akyurek]

On 2002-09-06 04:03, gnaga wrote:
Aladin I have tested your formula in my office machine it was working alright. But when I try the same thing in my home it does not work. I am getting an error of #REF

I need to open the source file then the error goes off. Both machines are running with Office XP.

Thanks

GNaga

The path that you use at work and at home must be different, whence the trouble.

 

[gnaga]

Aladin I am sorry, First time when I was trying at my office it seems to be working without opening the source file.

When I am trying now, it is working but the source file needs to be kept open.

Some one posted a message that if we use "INDIRECT" function then the source file have to be opened for updating the formula value.

So any other method is therr to update the formula without opening the source file.

Thanks

GNaga

 

[Aladin Akyurek]

On 2002-09-06 22:08, gnaga wrote:
Aladin I am sorry, First time when I was trying at my office it seems to be working without opening the source file.

When I am trying now, it is working but the source file needs to be kept open.

Some one posted a message that if we use "INDIRECT" function then the source file have to be opened for updating the formula value.

So any other method is therr to update the formula without opening the source file.

Thanks

GNaga

That someone should be me: I said "If you want to make filename a variable, you have to enclose the full path and the reference bit within INDIRECT. <font color=red>When you use INDIRECT, target files need to be open.</font>"

Any other method? No when you have to use INDIRECT.

 

[gnaga]

Ok. Thanks Aladin Let me try in VBA.
Thanks again

GNaga