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

 

[Dave Patton]

On 2002-09-04 06:48, gnaga wrote:
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

search for information on "Indirect"

you may also want to see help on "Index"

N.B. If you use Indirect, both files will have to be open.

 

[gnaga]

Thanks let me search for that. Sorry If the source file have to open means I do not require this formula rather I can copy and paste it.

Thanks
GNaga

 

[Aladin Akyurek]

On 2002-09-04 06:48, gnaga wrote:
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

The formula you attempt to use is flawed...

You can do the following:

( 1.) Activate Insert|Name|Define.
( 2.) Enter BigNum in the Names in Worbook box.
( 3.) Enter in the Refers to box:

9.99999999999999E+307

( 4.) Click OK.

Then use either

=LOOKUP(BigNum,[stock.xls]Sheet1!$C:$C)

or

=INDEX([Stock.xls]Sheet1!$C:$C,MATCH(BigNum,[Stock.xls]Sheet1!$C:$C))

It is obvious the first one is more efficient.

 

[gnaga]

=LOOKUP(BigNum,[stock.xls]Sheet1!$C:$C)

Yes Alading this will work fine. I want to supply the file name [stock.xls] from this workbook in which I am writing this formula. I hope it is clear.

For Ex.
I am writing this formula in the workbook Named Company.xls

Data in Sheet1 of Company.xls as follows

A B

1 Stock = Formula using C:GNagaA1.xls

2 Price = Formula using C:GNagaB1.xls

Is it not reuqired to give the full Path of the workbook?

Thank you

GNaga

 

[Aladin Akyurek]

On 2002-09-04 23:04, gnaga wrote:

=LOOKUP(BigNum,[stock.xls]Sheet1!$C:$C)

Yes Alading this will work fine. I want to supply the file name [stock.xls] from this workbook in which I am writing this formula. I hope it is clear.

For Ex.
I am writing this formula in the workbook Named Company.xls

Data in Sheet1 of Company.xls as follows

A B

1 Stock = Formula using C:GNagaA1.xls

2 Price = Formula using C:GNagaB1.xls

Is it not reuqired to give the full Path of the workbook?

Thank you

GNaga

Yes, you need the full path. For example:

=LOOKUP(BigNum,'C:GNaga[A1.xls]Sheet1'!C:C)

This works also with closed workbooks.

If you want to make filename a variable, you have to enclose the full path and the reference bit within INDIRECT. When you use INDIRECT, target files need to be open.

 

[gnaga]

Hi, Aladin the formula you have given is working. But when I enter the formula in a cell it opens the File Open dialog box and asking me to choose the file.

Secondly when I reopen the file it is not updating but saying "Workbook contains one or link can't update"

Any suggestion please.

Thanks
GNaga

 

[Aladin Akyurek]

On 2002-09-05 06:06, gnaga wrote:
Hi, Aladin the formula you have given is working. But when I enter the formula in a cell it opens the File Open dialog box and asking me to choose the file.

Secondly when I reopen the file it is not updating but saying "Workbook contains one or link can't update"

Any suggestion please.

Thanks
GNaga

You didn't use double 's I hope!

 

[gnaga]

Aladin If I use double \'s I am getting an error "#NA" as result. If I use single I have been asked to choose the file in a file open dialog box and the result is ok upon chosing the file.

If I type the file Name in the formula instead of giving the reference "A1.xls" then it works alright.

Any suggestion please?

Thanks

GNaga

 

[Aladin Akyurek]

On 2002-09-05 21:38, gnaga wrote:
Aladin If I use double \'s I am getting an error "#NA" as result. If I use single I have been asked to choose the file in a file open dialog box and the result is ok upon chosing the file.

If I type the file Name in the formula instead of giving the reference "A1.xls" then it works alright.

Any suggestion please?

Thanks

GNaga

I took A1.xls itself to be a file name...

Try:

=LOOKUP(BigNum,INDIRECT("'C:\GNaga\[" & A1 & ".xls]Sheet1'!C:C"))