software

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?

Hi!

Can you better describe how this is setup?

gt;first two columns in the worksheet are Project Status, Project Name.

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

Biff

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

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; Can you better describe how this is setup?
gt;
gt; gt;first two columns in the worksheet are Project Status, Project Name.
gt;
gt; That's easy enough to picture but what about the other sheet where you want
gt; the data extracted to?
gt;
gt; Biff
gt;
gt; quot;MichaelGquot; gt; wrote in message
gt; ...
gt; gt;I am working with a project status report where the first two columns in
gt; gt;the
gt; gt; worksheet are Project Status, Project Name. On another worksheet, in
gt; gt; columns
gt; gt; (by status) I have a set of simple counts of each status. Easy enough.
gt; gt; Below each of the counts by status, I would like to create a list of all
gt; gt; the
gt; gt; projects in that particular status. I've messed around with VLOOKUPS but
gt; gt; can
gt; gt; only find the first value in the range matching the criteria. I've also
gt; gt; messed around with vlookups in an IF statement, but still can't figure out
gt; gt; how to do this. Does anyone have ideas?
gt;
gt;
gt;

Ok, no problem.

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

Biff

quot;MichaelGquot; gt; wrote in message
...
gt; Sure can. On a second sheet in the same workbook, I have something that
gt; looks like this:
gt;
gt; Count of Status 1 Count of Status 2 Count of Status 3, etc.
gt;
gt; For that I'm using a simple =COUNTIF('Pipeline Data
gt; Sheet'!$E$3:$E$75,$A11)
gt; where E3:E75 is a column containing each project's status and A11 is the
gt; particular status.
gt;
gt; What I would like to see is something like this:
gt;
gt; Count of Status 1
gt; Project 1 in Status 1 (acutal name of project from Project Name column)
gt; Project 2 in Status 1
gt; Project 3 in Status 1
gt;
gt; ... and so on for each of the status columns. As the projects are moving
gt; through their life cycle their status changes so the lists need to
gt; dynamically update. This lists of project names are used as supplemental
gt; information on a pipeline chart. Until now I've been doing filters by
gt; project status then cutting and pasting data. Does that help?
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; Can you better describe how this is setup?
gt;gt;
gt;gt; gt;first two columns in the worksheet are Project Status, Project Name.
gt;gt;
gt;gt; That's easy enough to picture but what about the other sheet where you
gt;gt; want
gt;gt; the data extracted to?
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;MichaelGquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I am working with a project status report where the first two columns in
gt;gt; gt;the
gt;gt; gt; worksheet are Project Status, Project Name. On another worksheet, in
gt;gt; gt; columns
gt;gt; gt; (by status) I have a set of simple counts of each status. Easy
gt;gt; gt; enough.
gt;gt; gt; Below each of the counts by status, I would like to create a list of
gt;gt; gt; all
gt;gt; gt; the
gt;gt; gt; projects in that particular status. I've messed around with VLOOKUPS
gt;gt; gt; but
gt;gt; gt; can
gt;gt; gt; only find the first value in the range matching the criteria. I've
gt;gt; gt; also
gt;gt; gt; messed around with vlookups in an IF statement, but still can't figure
gt;gt; gt; out
gt;gt; gt; how to do this. Does anyone have ideas?
gt;gt;
gt;gt;
gt;gt;
Here's a sample file:

s43.yousendit.com/d.aspx?id=3...U0KXLP2OCJHIQK

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

quot;Biffquot; gt; wrote in message
...
gt; Ok, no problem.
gt;
gt; I'm getting ready to break for dinner but I'll get on it when I'm back.
gt;
gt; Biff
gt;
gt; quot;MichaelGquot; gt; wrote in message
gt; ...
gt;gt; Sure can. On a second sheet in the same workbook, I have something that
gt;gt; looks like this:
gt;gt;
gt;gt; Count of Status 1 Count of Status 2 Count of Status 3, etc.
gt;gt;
gt;gt; For that I'm using a simple =COUNTIF('Pipeline Data
gt;gt; Sheet'!$E$3:$E$75,$A11)
gt;gt; where E3:E75 is a column containing each project's status and A11 is the
gt;gt; particular status.
gt;gt;
gt;gt; What I would like to see is something like this:
gt;gt;
gt;gt; Count of Status 1
gt;gt; Project 1 in Status 1 (acutal name of project from Project Name column)
gt;gt; Project 2 in Status 1
gt;gt; Project 3 in Status 1
gt;gt;
gt;gt; ... and so on for each of the status columns. As the projects are moving
gt;gt; through their life cycle their status changes so the lists need to
gt;gt; dynamically update. This lists of project names are used as supplemental
gt;gt; information on a pipeline chart. Until now I've been doing filters by
gt;gt; project status then cutting and pasting data. Does that help?
gt;gt;
gt;gt; quot;Biffquot; wrote:
gt;gt;
gt;gt;gt; Hi!
gt;gt;gt;
gt;gt;gt; Can you better describe how this is setup?
gt;gt;gt;
gt;gt;gt; gt;first two columns in the worksheet are Project Status, Project Name.
gt;gt;gt;
gt;gt;gt; That's easy enough to picture but what about the other sheet where you
gt;gt;gt; want
gt;gt;gt; the data extracted to?
gt;gt;gt;
gt;gt;gt; Biff
gt;gt;gt;
gt;gt;gt; quot;MichaelGquot; gt; wrote in message
gt;gt;gt; ...
gt;gt;gt; gt;I am working with a project status report where the first two columns
gt;gt;gt; gt;in
gt;gt;gt; gt;the
gt;gt;gt; gt; worksheet are Project Status, Project Name. On another worksheet, in
gt;gt;gt; gt; columns
gt;gt;gt; gt; (by status) I have a set of simple counts of each status. Easy
gt;gt;gt; gt; enough.
gt;gt;gt; gt; Below each of the counts by status, I would like to create a list of
gt;gt;gt; gt; all
gt;gt;gt; gt; the
gt;gt;gt; gt; projects in that particular status. I've messed around with VLOOKUPS
gt;gt;gt; gt; but
gt;gt;gt; gt; can
gt;gt;gt; gt; only find the first value in the range matching the criteria. I've
gt;gt;gt; gt; also
gt;gt;gt; gt; messed around with vlookups in an IF statement, but still can't figure
gt;gt;gt; gt; out
gt;gt;gt; gt; how to do this. Does anyone have ideas?
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;
gt;
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!

quot;Biffquot; wrote:

gt; Here's a sample file:
gt;
gt; s43.yousendit.com/d.aspx?id=3...U0KXLP2OCJHIQK
gt;
gt; I'm sure it may need to be tweaked for your specific application but the
gt; formulas used demonstrate the technique.
gt;
gt; You need to copy the formulas to enough cells that they cover all the
gt; possible matches. For example, if the maximum number of projects in any
gt; status at any time is 10 then you need to copy the formulas to AT LEAST 10
gt; cells.
gt;
gt; The extraction formulas are array formulas. Select one and in the formula
gt; bar you'll notice the formula is enclosed in squiggly braces { }. These
gt; braces denote an array formula. When you type in these formulas instead of
gt; just hitting the ENTER key like you normally would you MUST use the key
gt; combination of CTRL,SHIFT,ENTER. When done properly Excel will enclose the
gt; formula in the braces. You can't just type them in, either. Also, if you
gt; edit an array formula it must be re-entered as an array using the key combo.
gt;
gt; Let me know if you need any futher assistance or explanations.
gt;
gt; Biff
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt; gt; Ok, no problem.
gt; gt;
gt; gt; I'm getting ready to break for dinner but I'll get on it when I'm back.
gt; gt;
gt; gt; Biff
gt; gt;
gt; gt; quot;MichaelGquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; Sure can. On a second sheet in the same workbook, I have something that
gt; gt;gt; looks like this:
gt; gt;gt;
gt; gt;gt; Count of Status 1 Count of Status 2 Count of Status 3, etc.
gt; gt;gt;
gt; gt;gt; For that I'm using a simple =COUNTIF('Pipeline Data
gt; gt;gt; Sheet'!$E$3:$E$75,$A11)
gt; gt;gt; where E3:E75 is a column containing each project's status and A11 is the
gt; gt;gt; particular status.
gt; gt;gt;
gt; gt;gt; What I would like to see is something like this:
gt; gt;gt;
gt; gt;gt; Count of Status 1
gt; gt;gt; Project 1 in Status 1 (acutal name of project from Project Name column)
gt; gt;gt; Project 2 in Status 1
gt; gt;gt; Project 3 in Status 1
gt; gt;gt;
gt; gt;gt; ... and so on for each of the status columns. As the projects are moving
gt; gt;gt; through their life cycle their status changes so the lists need to
gt; gt;gt; dynamically update. This lists of project names are used as supplemental
gt; gt;gt; information on a pipeline chart. Until now I've been doing filters by
gt; gt;gt; project status then cutting and pasting data. Does that help?
gt; gt;gt;
gt; gt;gt; quot;Biffquot; wrote:
gt; gt;gt;
gt; gt;gt;gt; Hi!
gt; gt;gt;gt;
gt; gt;gt;gt; Can you better describe how this is setup?
gt; gt;gt;gt;
gt; gt;gt;gt; gt;first two columns in the worksheet are Project Status, Project Name.
gt; gt;gt;gt;
gt; gt;gt;gt; That's easy enough to picture but what about the other sheet where you
gt; gt;gt;gt; want
gt; gt;gt;gt; the data extracted to?
gt; gt;gt;gt;
gt; gt;gt;gt; Biff
gt; gt;gt;gt;
gt; gt;gt;gt; quot;MichaelGquot; gt; wrote in message
gt; gt;gt;gt; ...
gt; gt;gt;gt; gt;I am working with a project status report where the first two columns
gt; gt;gt;gt; gt;in
gt; gt;gt;gt; gt;the
gt; gt;gt;gt; gt; worksheet are Project Status, Project Name. On another worksheet, in
gt; gt;gt;gt; gt; columns
gt; gt;gt;gt; gt; (by status) I have a set of simple counts of each status. Easy
gt; gt;gt;gt; gt; enough.
gt; gt;gt;gt; gt; Below each of the counts by status, I would like to create a list of
gt; gt;gt;gt; gt; all
gt; gt;gt;gt; gt; the
gt; gt;gt;gt; gt; projects in that particular status. I've messed around with VLOOKUPS
gt; gt;gt;gt; gt; but
gt; gt;gt;gt; gt; can
gt; gt;gt;gt; gt; only find the first value in the range matching the criteria. I've
gt; gt;gt;gt; gt; also
gt; gt;gt;gt; gt; messed around with vlookups in an IF statement, but still can't figure
gt; gt;gt;gt; gt; out
gt; gt;gt;gt; gt; how to do this. Does anyone have ideas?
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;
gt; gt;
gt;
gt;
gt;