Excell formula that autofills information

[blitzz008]

Hello Experts,

I am attaching an excel file with what I would like to do and with what
I think is 'half' of the formula.

Basically I have certain agents that manage cases. I get the
information of the cases each of the agents have and paste it in a tab
on my file. Once this happens I would like to automatically fill in
some columns and rows for each agent.
For example in the RAW tab is where I paste the information, I would
like excel to search for the agent in the D column and if it matches
the agent in the tab, to search for the matching row in column A and
then paste the case number in Tab John Column B Row 8, and so on.

I hope this is clear, if not please let me know.

Thanks a bunch !


+-------------------------------------------------------------------+
|Filename: example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4688 |
+-------------------------------------------------------------------+

 

[BruceP]

If each agent's information will remain consistent, you can use a
VLOOKUP() on the agent's name. I would recommend putting the lookup
table on its own, hidden sheet.

hth

Bruce

 

[Max]

Here's one play which automates it using non-array formulas ..

A sample construct is available at:
http://www.savefile.com/files/8145541
CallCentre_AutoGet AgentCases into own sheets.xls

In sheet: RAW (where the source data would be pasted),

Assume data is expected within A7:E30,
with the key col = col D (agent names)
Cols A to C are: case, no contact, days open

List the agent names in F6:J6 across: John, Peter, ...
Put in F7: =IF($D7="","",IF($D7=F$6,ROW(),""))
Copy F7 across to J7, fill down to say, J30
to cover the max expected extent of data

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

(The above defines WSN as a name we can use to
refer to the sheetname in formulas.
It will auto-extract the sheetname implicitly.
Technique came from a post by Harlan.)

Then in a new sheet named: John
With the same col headers pasted into A6:C6 (case, no contact, days open)

Put in A7:
=IF(ISERROR(SMALL(OFFSET(RAW!$E$7:$E$30,,MATCH(WSN,RAW!$F$6:$J$6,0)),ROW(A1)
)),"",INDEX(RAW!A$7:A$30,MATCH(SMALL(OFFSET(RAW!$E$7:$E$30,,MATCH(WSN,RAW!$F
$6:$J$6,0)),ROW(A1)),OFFSET(RAW!$E$7:$E$30,,MATCH(WSN,RAW!$F$6:$J$6,0)),0)))

Copy A7 across to C7, fill down to say, C20
(copy down by the smallest possible range sufficient
to cover the max expected extent for any single agent.
Here, I've assumed that 14 rows (rows 7 to 20) is sufficient.)

Cols A to C will return only the lines for the agent: John from "RAW",
with all lines neatly bunched at the top

Then just make a copy of the sheet: John,
rename it as, say: Peter
and we'd get the results for agent: Peter

Repeat the copy > rename sheet process
to get the rest of the agent sheets (a one-time job)

Adapt to suit ..

To refresh the data in RAW, just clear A7:E30 (use Delete key), and then
paste the new data in. Do not refresh by deleting the columns as this will
foul up the formulas.

---