Help me in Writing Formula.

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

=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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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"))
 

Forum statistics

Threads
1,144,113
Messages
5,722,559
Members
422,447
Latest member
srclife

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top