Searching and adding in a closed file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dr. Schwartz - 28 Jan 2008 12:13 GMT

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

Reply to this Message

Dave Peterson - 28 Jan 2008 15:00 GMT

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
>
[quoted text clipped - 23 lines]
> Any help is appreciated!
> The doctor

Signature

Dave Peterson

Reply to this Message

Dr. Schwartz - 29 Jan 2008 10:22 GMT

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:
>
[quoted text clipped - 35 lines]
> > Any help is appreciated!
> > The doctor

Reply to this Message

Dave Peterson - 29 Jan 2008 14:58 GMT

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.
[quoted text clipped - 42 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message