I changed it into
=IF(ROW(A1)COUNT($A:$A),"",INDEX([File2.xls]Sheet1!$B2:$B$200,MATCH(SMALL($A:$A,ROW(A1)),$A:$A ,0)+1))

And this seems to work, thanx for your help !
--
** Fool on the hill **


"Max" wrote:

One way ..

Assume source data in File2.xls, in Sheet1, with data expected within rows 3
to 102 (100 rows max, say). The key col is col D with dates running in D3
down, and the target col to return (into File1.xls) is col B (as posted)

With File2 open simultaneously,
In File1.xls, in say, Sheet1:

Put in A2:
=IF(ISNUMBER([File2.xls]Sheet1!D3),ROW(),"")

Put in B2:
=IF(ROW(A1)COUNT($A:$A),"",INDEX([File2.xls]Sheet1!B:B,MATCH(SMALL($A:$A,ROW(A1)),$A:$A,0)+1))

[Note: the "+1" at the end of the formula is just an arithmetic adjustment
since source data starts in row3 down, while we are extracting it here from
row2 down]

Then just select A2:B2, fill down to B101
to cover the max expected extent of 100 rows of source data in File2.xls
(Hide away the criteria col A if desired)

Col B will return the required results all neatly bunched at the top as
desired
(Change the commas in the formulas to semicolons to suit your continental
version)

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jaydubs" wrote:
Dear excel(lent) users,

When I make a comparison of two sets of information from two different files
I compare date from columns.

For instance in Column A I have data from file one (copy-c/Copy-v) and from
the second file I make a selection by using an if statement.

If a certain cell in column D in File 2 is filled (with a date), then I
want the entry from the cell of column B in the same row. I use
=ALS('[File2]Sheet1'!$D3<"";'[File2]Sheet1'!$B3;1) Where ALS is dutch
for IF.

When I perfom this I get the following sequence (for instance):
1356
1357
1358
1359


1362
1363

1365

1367
1368
1369
1370


The problem is that this gives me empty lines. How can I skip these empty
lines so it will only give me the entries that have a date in Column D. I
want the sequence to be like this:
1356
1357
1358
1359
1362
1363
1365
1367
1368
1369
1370

Can this be automated?

Thanks for your help !!
--
** Fool on the hill **