Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / March 2008

Tip: Looking for answers? Try searching our database.

Worksheet Function

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
sworr - 27 Feb 2008 21:43 GMT
I have a worksheet that has the following information:
  Initials of an employee, new loans signed up and all the relevant
information i.e.

       DJC    12345   50,000.00   MORTGAGE
       DJC    22456   10,000.00   1999 Chevrolet
       AAB    66442   25,000.00   OPERATING LOAN
       AAB    60077     9,000.00   UNSECURED

I have a separate worksheet that I need to list all of the loans under each
specific employee.  What formula would I use?  I've tried LOOKUP but it will
only find the first set of initials and data.
Signature

sworr

Don Guillett - 27 Feb 2008 21:46 GMT
data>filter>autofilter>filter on the initial column

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I have a worksheet that has the following information:
>   Initials of an employee, new loans signed up and all the relevant
[quoted text clipped - 10 lines]
> will
> only find the first set of initials and data.
Max - 02 Mar 2008 03:08 GMT
Think this formulas play will accomplish what you're after

As updates are made in the master: WSN (the "parent" sheet),
lines will be auto-copied by emp ID to each emp ID sheet (the "child"
sheets)  

Illustration in this sample, customized to your data as posted:
http://www.freefilehosting.net/download/3d0e4
AutoSlice lines by ID col to respective sheets.xls

One play which automates it using non-array formulas ..

In sheet: WS1 (the "master")
Assume source data as posted in cols A to D, data in row2 down,
with the key col = col A (Emp id)

List the emp IDs in K1 across: DJC, AAB, etc (can be in any order)
Put in K2: =IF($A2=K$1,ROW(),"")
Copy across as far as required, then fill down 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 after one of the emp ids, eg: AAB
With the same col headers pasted into A1:D1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX(WS1!A:A,MATCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),0)))

Copy A2 across to D2, fill down to say, D10 (copy down by the smallest
possible range sufficient to cover the max expected extent for any emp ID).
Here, I've assumed that 9 rows -- rows 2 to 10 -- is sufficient.

Cols A to C will return only the lines for the emp id: AAB from "WS1", with
all lines neatly bunched at the top

Now, just make a copy of the sheet: AAB, rename it as the next id: DJC, and
you'd get the results for that id.  Repeat the copy > rename sheet process to
get the rest of the id sheets (a one-time job) as required.  Adapt to suit ..
Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I have a worksheet that has the following information:
>    Initials of an employee, new loans signed up and all the relevant
[quoted text clipped - 8 lines]
> specific employee.  What formula would I use?  I've tried LOOKUP but it will
> only find the first set of initials and data.

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.