I have a portfolio spreadsheet with approx 100-200 rows where each row
depicts a unique project and each column depicts a calendar week. The cells
in each row are color coordinated to illustrate the phases of the project.
(For instance, I could have 3 consecutive tan cells illustrating analysis, 5
consecutive "no fill" cells depicting development, and 4 gray cells
indicating testing. It is actually a bit more colorful but I will keep it
simple for now!) In each cell there is a numeric value describing how many
projected resource hours are to be applied to that phase for that project
during that week.

I need to total the resource hours projected for any week by phase. For
instance I would like to look at a portfolio report and see the indivdidual
projects by row where the columns depict how may hours are being estimated by
week for the number of weeks being reported. At the bottom of each column I
would like to aggregate the hours estimated for the portfolio by phase for
each week.

I was hoping to use SUMIF to aggregate the time by phase for each week. I
thought I could use Names to "catagorize" the cells; however, it appears as
if I can only apply a Name (say "Test") to a specific cell or string of
cells. But I cannot seem to apply that same Name to multiple cells or string
of cells on multiple rows.

Any thoughts or suggestions would be appreciated! (Oh yea, I know I could
use Project Server but that is a whole other story!!!)

Thanks,

va

Hi, Va,

Try holding down the <Control key while you select multiple non-contiguous
cells or ranges of cells. Then, do Insert Name Define as usual - the range
name will apply to all the cells you selected.

Cheers

Pete



"va" wrote:

I have a portfolio spreadsheet with approx 100-200 rows where each row
depicts a unique project and each column depicts a calendar week. The cells
in each row are color coordinated to illustrate the phases of the project.
(For instance, I could have 3 consecutive tan cells illustrating analysis, 5
consecutive "no fill" cells depicting development, and 4 gray cells
indicating testing. It is actually a bit more colorful but I will keep it
simple for now!) In each cell there is a numeric value describing how many
projected resource hours are to be applied to that phase for that project
during that week.

I need to total the resource hours projected for any week by phase. For
instance I would like to look at a portfolio report and see the indivdidual
projects by row where the columns depict how may hours are being estimated by
week for the number of weeks being reported. At the bottom of each column I
would like to aggregate the hours estimated for the portfolio by phase for
each week.

I was hoping to use SUMIF to aggregate the time by phase for each week. I
thought I could use Names to "catagorize" the cells; however, it appears as
if I can only apply a Name (say "Test") to a specific cell or string of
cells. But I cannot seem to apply that same Name to multiple cells or string
of cells on multiple rows.

Any thoughts or suggestions would be appreciated! (Oh yea, I know I could
use Project Server but that is a whole other story!!!)

Thanks,

va

And if you want to drink coffe while selecting the non-contiguous cells you
can hit SHIFT + F8 to get into "ADD" mode and select cells without holding the
CTRL key.


Gord Dibben Excel MVP

On Fri, 4 Mar 2005 02:09:02 -0800, Peter Rooney
wrote:

Hi, Va,

Try holding down the <Control key while you select multiple non-contiguous
cells or ranges of cells. Then, do Insert Name Define as usual - the range
name will apply to all the cells you selected.

Cheers

Pete



"va" wrote:

I have a portfolio spreadsheet with approx 100-200 rows where each row
depicts a unique project and each column depicts a calendar week. The cells
in each row are color coordinated to illustrate the phases of the project.
(For instance, I could have 3 consecutive tan cells illustrating analysis, 5
consecutive "no fill" cells depicting development, and 4 gray cells
indicating testing. It is actually a bit more colorful but I will keep it
simple for now!) In each cell there is a numeric value describing how many
projected resource hours are to be applied to that phase for that project
during that week.

I need to total the resource hours projected for any week by phase. For
instance I would like to look at a portfolio report and see the indivdidual
projects by row where the columns depict how may hours are being estimated by
week for the number of weeks being reported. At the bottom of each column I
would like to aggregate the hours estimated for the portfolio by phase for
each week.

I was hoping to use SUMIF to aggregate the time by phase for each week. I
thought I could use Names to "catagorize" the cells; however, it appears as
if I can only apply a Name (say "Test") to a specific cell or string of
cells. But I cannot seem to apply that same Name to multiple cells or string
of cells on multiple rows.

Any thoughts or suggestions would be appreciated! (Oh yea, I know I could
use Project Server but that is a whole other story!!!)

Thanks,

va