I typed your number (1018) into some cells in columns D and J of Book3
In Book2 in A1 I entered your number (1018)
In B1 I entered =COUNTIF([Book3]Sheet1!$D$1:$D$18,A1) to count the number of
occurrences in column D of the other file
Or I could use =COUNTIF([Book3]Sheet1!$D$1:$J$18,A1) to count the number of
occurrences in a block
If the sheet name has spaces in it you need something like
=COUNTIF('[Book3]Nov Data'!$D$1:$D$18,A1) Note the single quotes

It is best enter the formula in this manner:
type =COUNTIF(
open the second file and with the mouse select the range of cells to get
'[Book3]Nov Data'!$D$1:$D$18
return to first book
complete formula with: ,A1)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Funkyfido" wrote in message
...
Is there a formula that I could use to perform the following.

In one spreadsheet I have a unique no e.g.1018. In another spreadsheet I
have lots of rows some of which have the unique no 1018. I need a formula
to
say that if any rows in the second spreadsheet equal 1018 then return all
those rows.

Thanks