Create list of text matching criteria

M

MichaelG

I am working with a project status report where the first two columns in the
worksheet are Project Status, Project Name. On another worksheet, in columns
(by status) I have a set of simple counts of each status. Easy enough.
Below each of the counts by status, I would like to create a list of all the
projects in that particular status. I've messed around with VLOOKUPS but can
only find the first value in the range matching the criteria. I've also
messed around with vlookups in an IF statement, but still can't figure out
how to do this. Does anyone have ideas?
 
B

Biff

Hi!

Can you better describe how this is setup?
first two columns in the worksheet are Project Status, Project Name.
Click to expand...

That's easy enough to picture but what about the other sheet where you want
the data extracted to?

Biff
 
M

MichaelG

Sure can. On a second sheet in the same workbook, I have something that
looks like this:

Count of Status 1 Count of Status 2 Count of Status 3, etc.

For that I'm using a simple =COUNTIF('Pipeline Data Sheet'!$E$3:$E$75,$A11)
where E3:E75 is a column containing each project's status and A11 is the
particular status.

What I would like to see is something like this:

Count of Status 1
Project 1 in Status 1 (acutal name of project from Project Name column)
Project 2 in Status 1
Project 3 in Status 1

.... and so on for each of the status columns. As the projects are moving
through their life cycle their status changes so the lists need to
dynamically update. This lists of project names are used as supplemental
information on a pipeline chart. Until now I've been doing filters by
project status then cutting and pasting data. Does that help?
 
B

Biff

Ok, no problem.

I'm getting ready to break for dinner but I'll get on it when I'm back.

Biff
 
B

Biff

Here's a sample file:

http://s43.yousendit.com/d.aspx?id=320M1P9AWQPIU0KXLP2OCJHIQK

I'm sure it may need to be tweaked for your specific application but the
formulas used demonstrate the technique.

You need to copy the formulas to enough cells that they cover all the
possible matches. For example, if the maximum number of projects in any
status at any time is 10 then you need to copy the formulas to AT LEAST 10
cells.

The extraction formulas are array formulas. Select one and in the formula
bar you'll notice the formula is enclosed in squiggly braces { }. These
braces denote an array formula. When you type in these formulas instead of
just hitting the ENTER key like you normally would you MUST use the key
combination of CTRL,SHIFT,ENTER. When done properly Excel will enclose the
formula in the braces. You can't just type them in, either. Also, if you
edit an array formula it must be re-entered as an array using the key combo.

Let me know if you need any futher assistance or explanations.

Biff
 
M

MichaelG

Can you repost your sample file? I didn't get a chance to get out here for
several days and the file has expired.

Thanks!
 
Top