Copy to another worksheet IF

M

mandy1979

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
 
P

Pete_UK

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$2:D2,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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum If multiple conditions 3
Using lookup functions with conditions and constantly changing dat 11
IF FUNCTION? 6
How to sum cells based on conditions 2
what formula do I use 2
Copy data from one worksheet to another 5
Summing like data 5
Sum value between two dates and copy to new cell 7

Top