Copy to another worksheet IF

I have a workbook with multiple pages. There is a master page that I have
titled Hot Sheet. It contains all of the data for all agents. The other
sheets contain the same data, but only for a specific agent. Currently, I
have to type the data onto the Hot Sheet and to the perspective agent's
sheet. I want the data in a row to copy to the agents correct sheet,
omitting blank spaces. The filtering factor could either be agent name that
I input in column C of the Hot Sheet or the color of the row, because each
row is highlighted with a color that is specific to that agent. Ex:

ID DATE AGENT CLIENT ADDRESS ETC
1234 01/09/10 FRANK DOE, JOHN 121 No Name Road etc
3274 01/10/10 BRENT DOE, JANE 321 No Name Street etc
5978 12/12/09 CHAY SMITH, JON 972 My Steet etc
6547 11/11/09 FRANK JONES, JANE 111 Your Street etc

Above is the hotsheet. Now, I need to automatically fill Frank's sheet as
follows:
ID DATE AGENT CLIENT ADDRESS ETC
1234 01/09/10 FRANK DOE, JOHN 121 No Name Road etc
6547 11/11/09 FRANK JONES, JANE 111 Your Street etc

Insert a new column A in Hotsheet, give it a title like "Ref" in A1
and put this formula in A2:

=IF(D2="","-",D2&"_"&COUNTIF(D$22,D2))

then copy this down beyond your data as far as you think you will need
as the file grows (eg row 10,000) - it will give you something like
this:

Ref
FRANK_1
BRENT_1
CHAY_1
FRANK_2
-
-

This gives a unique reference for each agent, made up of the agent's
name followed by a sequential number. The hyphens show where the
formula has been copied to (in this case row 7).

In the sheets for your agents you will also need to insert a new
column A. Then in Frank's sheet you will need this formula in A2:

=IF(ISNA(MATCH("FRANK"&"_"&ROW(A1),'Hot Sheet'!A:A,
0)),"",MATCH("FRANK"&"_"&ROW(A1),'Hot Sheet'!A:A,0))

and copy this down as far as you think you will need. You will need
similar formulae for the other agent's sheets, but you will change the
name FRANK to the name of the agent. What this does is to give you the
rows where the agent's records can be found.

Then in B2 of all the agents' sheets you will need this formula:

=IF($A2="","",INDEX('Hot Sheet'!B:B,$A2))

and then copy this across as far as you need. You will need to apply
formatting to each column (eg column C is a date), and then you can
copy this down as far as you think is needed to accommodate your data
- it doesn't matter if you copy too far as you will only get blanks,
and you need to cater for new data being added.

I've assumed that you are using row 1 for the headings throughout.

Finally, if you wish, you can hide all those new column As, so that
your sheets look exactly the same as you have them now. Note that the
colours that you apply in Hotsheet will not be transferred to the
agent's sheets by these formulae.

Hope this helps.

Pete

On Feb 11, 5:33*am, mandy1979
wrote:
I have a workbook with multiple pages. *There is a master page that I have
titled Hot Sheet. *It contains all of the data for all agents. *The other
sheets contain the same data, but only for a specific agent. *Currently, I
have to type the data onto the Hot Sheet and to the perspective agent's
sheet. *I want the data in a row to copy to the agents correct sheet,
omitting blank spaces. *The filtering factor could either be agent name that
I input in column C of the Hot Sheet or the color of the row, because each
row is highlighted with a color that is specific to that agent. *Ex:

ID * * *DATE * * * *AGENT * CLIENT * * * *ADDRESS * * * * * * * * *ETC
1234 *01/09/10 *FRANK * *DOE, JOHN * 121 No Name Road * * etc
3274 *01/10/10 *BRENT * *DOE, JANE * *321 No Name Street * etc
5978 *12/12/09 *CHAY * * *SMITH, JON *972 My Steet * * * * * * etc
6547 *11/11/09 *FRANK * *JONES, JANE 111 Your Street * * * * etc

Above is the hotsheet. * Now, I need to automatically fill Frank's sheet as
follows:
ID * * *DATE * * * *AGENT * CLIENT * * * *ADDRESS * * * * * * * * *ETC
1234 *01/09/10 *FRANK * *DOE, JOHN * 121 No Name Road * *etc
6547 *11/11/09 *FRANK * *JONES, JANE 111 Your Street * * * *etc

Pete!

Not trying to hijack this thread but I have a similar problem, its the
other way around though, I need to copy data from multiple sheets into one,
can you please help? Basically, i need a formula to say if E#=yes then copy
A# to worksheet "monthly review"

I made a post about it with more detail...

http://www.microsoft.com/office/comm...sloc=en-us&p=1

Thanks so much!

Robbie

"Pete_UK" wrote:

Insert a new column A in Hotsheet, give it a title like "Ref" in A1
and put this formula in A2:

=IF(D2="","-",D2&"_"&COUNTIF(D$22,D2))

then copy this down beyond your data as far as you think you will need
as the file grows (eg row 10,000) - it will give you something like
this:

Ref
FRANK_1
BRENT_1
CHAY_1
FRANK_2
-
-

This gives a unique reference for each agent, made up of the agent's
name followed by a sequential number. The hyphens show where the
formula has been copied to (in this case row 7).

In the sheets for your agents you will also need to insert a new
column A. Then in Frank's sheet you will need this formula in A2:

=IF(ISNA(MATCH("FRANK"&"_"&ROW(A1),'Hot Sheet'!A:A,
0)),"",MATCH("FRANK"&"_"&ROW(A1),'Hot Sheet'!A:A,0))

and copy this down as far as you think you will need. You will need
similar formulae for the other agent's sheets, but you will change the
name FRANK to the name of the agent. What this does is to give you the
rows where the agent's records can be found.

Then in B2 of all the agents' sheets you will need this formula:

=IF($A2="","",INDEX('Hot Sheet'!B:B,$A2))

and then copy this across as far as you need. You will need to apply
formatting to each column (eg column C is a date), and then you can
copy this down as far as you think is needed to accommodate your data
- it doesn't matter if you copy too far as you will only get blanks,
and you need to cater for new data being added.

I've assumed that you are using row 1 for the headings throughout.

Finally, if you wish, you can hide all those new column As, so that
your sheets look exactly the same as you have them now. Note that the
colours that you apply in Hotsheet will not be transferred to the
agent's sheets by these formulae.

Hope this helps.

Pete

On Feb 11, 5:33 am, mandy1979
wrote:
I have a workbook with multiple pages. There is a master page that I have
titled Hot Sheet. It contains all of the data for all agents. The other
sheets contain the same data, but only for a specific agent. Currently, I
have to type the data onto the Hot Sheet and to the perspective agent's
sheet. I want the data in a row to copy to the agents correct sheet,
omitting blank spaces. The filtering factor could either be agent name that
I input in column C of the Hot Sheet or the color of the row, because each
row is highlighted with a color that is specific to that agent. Ex:

ID DATE AGENT CLIENT ADDRESS ETC
1234 01/09/10 FRANK DOE, JOHN 121 No Name Road etc
3274 01/10/10 BRENT DOE, JANE 321 No Name Street etc
5978 12/12/09 CHAY SMITH, JON 972 My Steet etc
6547 11/11/09 FRANK JONES, JANE 111 Your Street etc

Above is the hotsheet. Now, I need to automatically fill Frank's sheet as
follows:
ID DATE AGENT CLIENT ADDRESS ETC
1234 01/09/10 FRANK DOE, JOHN 121 No Name Road etc
6547 11/11/09 FRANK JONES, JANE 111 Your Street etc

.

Hi,

You may refer to question 7 at the following link -
http://ashishmathur.com/knowledgebaseII.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"mandy1979" wrote in message
...
I have a workbook with multiple pages. There is a master page that I have
titled Hot Sheet. It contains all of the data for all agents. The other
sheets contain the same data, but only for a specific agent. Currently, I
have to type the data onto the Hot Sheet and to the perspective agent's
sheet. I want the data in a row to copy to the agents correct sheet,
omitting blank spaces. The filtering factor could either be agent name
that
I input in column C of the Hot Sheet or the color of the row, because each
row is highlighted with a color that is specific to that agent. Ex:

ID DATE AGENT CLIENT ADDRESS ETC
1234 01/09/10 FRANK DOE, JOHN 121 No Name Road etc
3274 01/10/10 BRENT DOE, JANE 321 No Name Street etc
5978 12/12/09 CHAY SMITH, JON 972 My Steet etc
6547 11/11/09 FRANK JONES, JANE 111 Your Street etc

Above is the hotsheet. Now, I need to automatically fill Frank's sheet
as
follows:
ID DATE AGENT CLIENT ADDRESS ETC
1234 01/09/10 FRANK DOE, JOHN 121 No Name Road etc
6547 11/11/09 FRANK JONES, JANE 111 Your Street etc