I wonder if someone can help me out. I have a workbook that contains
multiple worksheets, one of which is a master data sheet.

I would like to copy specific data from this sheet to multiple
worksheets based on specific criteria.

Master Data Worksheet is setup as follows:

Date Salesrep Customer Project $ Status, etc.
Aug A ABC etc.
Aug A DEF etc.
Aug B GHI etc.
Aug C JKL etc.
Aug C MNO etc.
Aug D PQR etc.

I would like the macro to lookup the Salesrep name and copy the enitre
row for that rep over to the specific Salesreps workbook and then save
this data.

Thanks in advance for your help.


Mark.

I want both rows for salesrep A, etc. In reality there are about 100
rows and 8 sales people.


Mark.

On Aug 27, 11:26*am, Mark767 wrote:
I want both rows for salesrep A, etc. *In reality there are about 100
rows and 8 sales people.

Mark.

I should also point out that I am not just copying to a new sheet but
a new workbook.


Mark.

Mark,

You can extract multiple lines/and columns of data using an array formula like this (enter using
Ctrl-Shift-Enter):

=IF(COUNTIF('Data Sheet'!$A:$A,$A$1)=ROWS($A$1:A1),INDEX('Data Sheet'!A:A,LARGE(('Data
Sheet'!$A$1:$A$999=$A$1)*ROW('Data Sheet'!$A$1:$A$999),COUNTIF('Data
Sheet'!$A:$A,$A$1)-(ROWS($B$1:B1)-1))),"")

copied down and over, where A1 has the rep name, and the rep names are found in column A of "Data
Sheet"

HTH,
Bernie
MS Excel MVP


"Mark767" wrote in message
...
On Aug 27, 11:26 am, Mark767 wrote:
I want both rows for salesrep A, etc. In reality there are about 100
rows and 8 sales people.

Mark.

I should also point out that I am not just copying to a new sheet but
a new workbook.


Mark.

One final thing, the number of rows for each sales person will change
each month so the individual salesman workbooks will somehow need to
be cleared first before the new data is copied over.



Mark.

=IF(COUNTIF('Data Sheet'!$A:$A,$A$1)=ROWS($A$1:A1),INDEX('Data Sheet'!
A:A,LARGE(('Data
Sheet'!$A$1:$A$999=$A$1)*ROW('Data Sheet'!$A$1:$A$999),COUNTIF('Data
Sheet'!$A:$A,$A$1)-(ROWS($B$1:B1)-1))),"")

Thanks for the help, unfortunately I can't get it to work. Here are
my steps.

In the new workbook for the individual salesrep, I entered this
formula and changed A1 to be the sales rep name "John".
In my actual case the sales rep names on in a workbook called "Sales
Data" on a sheet called "Master". They start below
the title information in column E6 so I changed 'Data Sheet' to be
'Master' and got a #VALUE error. Also not sure how to take
care of rep names starting in E6.


Mark.

Bernie,

I wonder if you take another look at this. I am currently unable to
move forward.

thanks in advance.



Mark.

Mark,

Try this array formula (enter using Ctrl-Chift-Enter) - then copy down and over.

=IF(COUNTIF('[Sales Data.xls]Master'!$E:$E,$A$1)=ROWS($A$1:A1),INDEX('[Sales
Data.xls]Master'!E$1:E$1000,LARGE(('[Sales Data.xls]Master'!$E$1:$E$1000=$A$1)*ROW('[Sales
Data.xls]Master'!$E$1:$E$1000),COUNTIF('[Sales Data.xls]Master'!$E:$E,$A$1)-(ROWS($B$1:B1)-1))),"")

HTH,
Bernie
MS Excel MVP


"Mark767" wrote in message
...

Bernie,

I wonder if you take another look at this. I am currently unable to
move forward.

thanks in advance.



Mark.

Bernie,

I am still getting the #Value! error.

I even went as far as making a new spreadsheet with just numbers in
each row and column and trying it again using your formula above in a
new worksheet all with the same names. Again the #Value! error, which
is strange because it was all numbers so I am not sure how the data
types could be mixed.

Any ideas ?


Mark.

#VALUE! could indicate you did not array-enter the formula.

CTRL + SHIFT + ENTER


Gord Dibben MS Excel MVP

On Tue, 1 Sep 2009 08:13:36 -0700 (PDT), Mark767
wrote:

Bernie,

I am still getting the #Value! error.

I even went as far as making a new spreadsheet with just numbers in
each row and column and trying it again using your formula above in a
new worksheet all with the same names. Again the #Value! error, which
is strange because it was all numbers so I am not sure how the data
types could be mixed.

Any ideas ?


Mark.

Mark,

The formulas will return #Value! if the data workbook is closed. Have both workbooks open, and the
formulas should be fine - it doesn't matter what type of data you have, since you are simply
returning the value via, ultimately, the INDEX function.

In case you have further problems, I have sent two files with working formnulas to your gmail
address - save them to the same folder, and open both at the same time.

HTH,
Bernie
MS Excel MVP


"Mark767" wrote in message
...
Bernie,

I am still getting the #Value! error.

I even went as far as making a new spreadsheet with just numbers in
each row and column and trying it again using your formula above in a
new worksheet all with the same names. Again the #Value! error, which
is strange because it was all numbers so I am not sure how the data
types could be mixed.

Any ideas ?


Mark.

Gord,

I get #NUM! with non-array entry, and #VALUE! with the data workbook closed.

HTH,
Bernie
MS Excel MVP


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
#VALUE! could indicate you did not array-enter the formula.

CTRL + SHIFT + ENTER


Gord Dibben MS Excel MVP

On Tue, 1 Sep 2009 08:13:36 -0700 (PDT), Mark767
wrote:

Bernie,

I am still getting the #Value! error.

I even went as far as making a new spreadsheet with just numbers in
each row and column and trying it again using your formula above in a
new worksheet all with the same names. Again the #Value! error, which
is strange because it was all numbers so I am not sure how the data
types could be mixed.

Any ideas ?


Mark.

On Sep 1, 11:32*am, Gord Dibben <gorddibbATshawDOTca wrote:
#VALUE! could indicate you did not array-enter the formula.

CTRL + SHIFT + ENTER

Gord Dibben *MS Excel MVP

On Tue, 1 Sep 2009 08:13:36 -0700 (PDT), Mark767
wrote:

Bernie,

I am still getting the #Value! error.

I even went as far as making a new spreadsheet with just numbers in
each row and column and trying it again using your formula above in a
new worksheet all with the same names. *Again the #Value! error, which
is strange because it was all numbers so I am not sure how the data
types could be mixed.

Any ideas ?

Mark.



Thanks for the help. I have it working now. The problem was that I
did not have both spreadsheets in the same folder and open.


Mark.