Searching and adding in a closed file

[Dr. Schwartz]

Excel 2003

From file test1.xls I want to run a macro that looks for data in a closed
file (source.xls). The data in source.xls is arranged like this:

ColA ColB
cp1 01
cp2 01
cp3 01
cp1 02
cp3 02
cp1 03
cp2 02

The lookup function I need asks the question: What is the next number (in
ColB) for cp1? The answer is 04.

cp2 = 03
cp3 = 03

I want to do the lookup without opening the file. Is this possible?

Do I need to open source.xls to add a new line of data? Or can it be done
"behind the scenes"?

Any help is appreciated!
The doctor

 

[Dave Peterson]

You could use a formula like:

=LOOKUP(2,1/('C:\My Documents\excel\[book3.xls]Sheet1'!$A$1:$A$20=$A$1),
'C:\My Documents\excel\[book3.xls]Sheet1'!$B$1:$B$20)+1

I put CP1 in A1 of the activesheet and that formula in B1.

You can adjust the range (I used rows 1:20), but you can't use the whole column
until xl2007.)

But the bigger range you use, the longer time to calculate.

 

[Dr. Schwartz]

Hi Dave, Unfortunately I need to work with the result in my script, so a
formula wont work for me.

You could use a formula like:

=LOOKUP(2,1/('C:\My Documents\excel\[book3.xls]Sheet1'!$A$1:$A$20=$A$1),
'C:\My Documents\excel\[book3.xls]Sheet1'!$B$1:$B$20)+1

I put CP1 in A1 of the activesheet and that formula in B1.

You can adjust the range (I used rows 1:20), but you can't use the whole column
until xl2007.)

But the bigger range you use, the longer time to calculate.

Excel 2003

From file test1.xls I want to run a macro that looks for data in a closed
file (source.xls). The data in source.xls is arranged like this:

ColA ColB
cp1 01
cp2 01
cp3 01
cp1 02
cp3 02
cp1 03
cp2 02

The lookup function I need asks the question: What is the next number (in
ColB) for cp1? The answer is 04.

cp2 = 03
cp3 = 03

I want to do the lookup without opening the file. Is this possible?

Do I need to open source.xls to add a new line of data? Or can it be done
"behind the scenes"?

Any help is appreciated!
The doctor

 

[Dave Peterson]

Maybe you could find an unused cell, plop the formula in it, read the value, and
clean up the cell.

Hi Dave, Unfortunately I need to work with the result in my script, so a
formula wont work for me.

You could use a formula like:

=LOOKUP(2,1/('C:\My Documents\excel\[book3.xls]Sheet1'!$A$1:$A$20=$A$1),
'C:\My Documents\excel\[book3.xls]Sheet1'!$B$1:$B$20)+1

I put CP1 in A1 of the activesheet and that formula in B1.

You can adjust the range (I used rows 1:20), but you can't use the whole column
until xl2007.)

But the bigger range you use, the longer time to calculate.

Excel 2003

From file test1.xls I want to run a macro that looks for data in a closed
file (source.xls). The data in source.xls is arranged like this:

ColA ColB
cp1 01
cp2 01
cp3 01
cp1 02
cp3 02
cp1 03
cp2 02

The lookup function I need asks the question: What is the next number (in
ColB) for cp1? The answer is 04.

cp2 = 03
cp3 = 03

I want to do the lookup without opening the file. Is this possible?

Do I need to open source.xls to add a new line of data? Or can it be done
"behind the scenes"?

Any help is appreciated!
The doctor