Yup...You caught a typo. Sorry about that....Good catch, though. Thanks for
letting me know.

***********
Regards,
Ron


"sam" wrote:

Hi Ron

That is an interesting slant.

Maybe a typo in the setup "Names in workbook: Sheet1!rngSource" should
be "Names in workbook: Sheet2!rngSource"??

I had a couple of VBA solutions of my own, one using ADO with a query
string to open a recordset with the relevant Accounts and one using the
..find , .findnext methods to get the appropiate range. However, I need
to distribute this without any macros hence the need for a formula solution.

Thanks for your time anyway
Regards
Sam

Ron Coderre wrote:
Here's something to try:

Assumptions:
On Sheet1 contains your data in cells A1:B10
On Sheet2 is where you want the extracted data to be displayed

So....
Using Sheet2:
A1: ID
B1: Account

InsertNameDefine
Names in workbook: Sheet2!rngDest
Refers to: =Sheet2!$A$1:$B$1

I1: ID
I2: 1

InsertNameDefine
Names in workbook: Sheet2!rngCriteria
Refers to: =Sheet2!$I$1:$I$2

Next...still on Sheet2:
InsertNameDefine
Names in workbook: Sheet1!rngSource
Refers to: =Sheet1!$A$1:$B$10

(Notice: you are on Sheet2, but creating a Sheet2 level range name, but
the referenced range is on Sheet1)

The reason: An advanced filter cannot SEND data to another sheet, but
it can PULL data from another sheet.

Next: In a general vba module, enter this code:

Option Explicit
Sub PullMatchingData()
Range("Sheet2!rngSource").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("Sheet2!rngCriteria"), _
CopyToRange:=Range("Sheet2!rngDest"), _
Unique:=False
End Sub

To run the code:
ToolsMacroMacros (or [Alt]+[F8])
Select and run: PullMatchingData

Change the value of I2 to 3 and run it again.

Does that help?

***********
Regards,
Ron


"sam" wrote:


Hi All

On Sheet1 I have data as follows:
ID Account
1 500
1 510
1 550
2 500
2 505
2 600
2 650
3 400
etc

Each ID will be repeated between 1 and 39 times.

On Sheet2 I would like to be able to enter an ID number in A1 and have
the relevant Account numbers displayed in the range A2:AM2.

Many thanks in advance
Sam