Catagorize Column Data by Name

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

0
VA (10)
3/3/2005 5:09:09 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
508 Views

Similar Articles

[PageSpeed] 51

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
> 
0
PeterRooney (105)
3/4/2005 10:09:02 AM
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
<PeterRooney@discussions.microsoft.com> 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
>> 

0
Gord
3/4/2005 5:35:46 PM
Reply:

Similar Artilces:

Changing A Column Of Text numbers To True Numbers: How ?
Hello, Using Excel 2007 Have a column of numbers that I entered as Text. Wish to change the entire column, now, to "Numbers," so that I can apply a formula (SUM) to them. Apparently, it won't let me, even though I select the column and go to "Format Cells" and select Numbers. Still comes up as a Text column. How to I change them to real "numbers", please ? Thanks, Bob Robert11 wrote: > Hello, > > Using Excel 2007 > > Have a column of numbers that I entered as Text. > > Wish to change the entire column, now, to "Numbers...

Understanding Column Chart Capabilities within Excel
I am trying to build a single column type graph. There are 4 categories of values to graph. Each category has a maximum and minimum value, which will be different values. All of the available column graphs forcethe column bottoms (minimums) all the way to the X axis. Since my minimum values for each category have independent and different minimums, I want to "suspend" them above the X axis. I need to know if (and how) it can be done. I have made an image of what I am trying to do in an excel file that I can send, if my explanation is confusing. Thanks WJW Not sure if I unders...

how do I wrap a column in a report?
I have a report that has only 2 very long columns. I want to wrap the columns so there are three groups of columns on each page. I'm just starting to learn VBA, but can't find anything in my book that I understand enough to help me. ...

Sample Range of Data
I have created a sample using data analysis but I want the sample to include all of the columns, not just the sample #. For example if I use data analysis to select a random sample from a worksheet it will return just the sample area I selected. It appears I can only sample on numeric values so I have and the numbers 1, 2, 3....in the first column. What I would like to see is when it returns the sample that it returns all columns not just the first one. Hope I made sense. Jamie ...

Transposing a column to several rows
I have a large file with many rows; let's say it looks like this: start data1 data2 data3 data4 start data1 data2 start data1 data2 data3 .... What I want to do is transpose the column to a row, and then each time the word START appears, start a new row. So the above would look like this: start data1 data2 data3 data4 start data1 data2 start data1 data2 data3 .... Any ideas? Thanks in advance, Scott Make sure you make a backup copy of your data before using the code I just posted "walkersc@gmail.com" wrote: > I have a large file with many rows; let's say i...

CDialogBar in a CDialog Appl. for HeaderCtrl Column Chooser?
I am trying to implement a "Column Chooser" for a Report style ListCtrl in a Dialog based application - very much like Outlook's "Field Chooser" or Maarten Hoeben's "Report control - an Outlook 2000 style SuperGrid Report Control", at The Code Project. My problems so far are: a. Maarten Hoeben's solution is way over the top for me, when I already have an existing application with a ListCtrl and HeaderCtrl already working (except for this extra feature!). b. The CDialogBar solution is very neat and looks great but it is based on CFrameWnd, which c...

Change Query Field Name
Hello, I have a query with the field name "LastName". When I pull a report and export it to excel at the top of the column it come out as "LastName". How can I change it in the query so when I export to excel the column heading says "Customer/Entity Name? Thanks!! Use an alias: SELECT LastName AS [Customer/Entity Name] FROM TableName; -- Ken Snell <MS ACCESS MVP> "Stockwell43" <Stockwell43@discussions.microsoft.com> wrote in message news:D8C930C5-902F-415B-B3C9-F82BE6DF4FD9@microsoft.com... > Hello, > > I have a query...

Combining two data sources in a Pivot table.
Hi, I have a large CSV data file that I am using as an "external data source" for a Pivot table in an (autoupdate) template. That works fine. It's the sales file created automatically by an application. So it's "sales.csv" and my template with the Pivot table is "sales.xlt". But I also have costs, so I have another file where costs are recorded manually. The data is in "Costs.xls" and I will have another Pivot table where I analyse the costs : "Costs.xlt" Now I want to offset Costs against Sales, so I am looking for some way to ...

named properties limit reached, what could happen?
Currently the limit for named properties is set to 20000 and we are again receiving the event 9667. My question is, what problems could occur if we don't raise the limit more or fix the problem by moving the mailbox to another server? Will Exchange continue to work? -thanks On SBS? I have never seen this on SBS... can you confirm? Meanwhile there are a great number of articles on the web as to how to fix or work around this. -Please post the resolution to your issue so others may benefit. -Get Your SBS Health Check at www.sbsbpa.com > Currently the limi...

Calendar Spreadsheet: Column 1 = Date, Column 2 Time of Day, Column 3 memo text field
I am an excel novice. Looking for a template or instructions on how to create a Calendar Spread Sheet with a single line for each day. I.E. Left Hand Column Would be a date Column Friday June 1st, 2007 Saturday June 2nd, 2007 or to make it more simple Friday 06/01/07 Saturday 06/02/07 .... and so on (want to start with 2006) 2nd Column should be time of day Want to be able to manually insert a duplicate a line, e.g. more than one event on a given day. Like to know how to create additional columns that are true false and/or time of day fields. Also want a final column that is a variable...

Outlook formatting of columns to wrap text in Task view
I have used multi-line layout to view tasks but it does not show in print preview. Is it possible to wrap text if the number of characters exceed the column width? If so, can we print it in the same format? Would appreciate your reply. Thanks. Jay ...

Auto fill Column based on data in other columns
I have a report I'm downloading into Excel each day. Columns A, B, and C have the same number of rows populated. Columns D and E don't. I want to populate columns D and E with code the same number of rows Columns A, B and C are. I want to auto fill Column D with the text "ABCD" and Column E would be the current date in 06/05/06 format. I would like to put this code into an existing Macro. Any help you can give me is appreciated. Double click on the fill-handle http://www.mvps.org/dmcritchie/excel/fillhand.htm If you want to simulate with a macro Filldown as...

Repeating Style Columns in Draft View ?
In Draft View of Word 2008, when I use the horizontal scrollbar or that feature on the mouse/keypad, I will end up with repeats of the left hand style column; 3, 4, 5 of them. I switch to another View and return and all is well with the "style" column showing. Am I doing something wrong or is this a bug or something else? Thanks. -- Norm That's a "feature". Draft is a special view designed to maximise computing efficiency. It has many purposes, but its major purpose is to work on big documents (over 200 pages) and to make editing easier by showi...

Map XSD/XML data to VB.net classes
Hi, That's my first post here so I hope this is the right group to post to. I have to design a good strategy to manipulate XML data in VB.net language. Here's the business case: - I will have a large number (200+) of XML forms created with Infopath that represents industrial machines specifications. Infopath makes a XSD available for each form template. - These forms templates will be maintained by non programmers. - I am building an application that gathers form data to compute several indicators (cost, volume, weight...) based on the data inside the XML files created by Infopath. I...

Expand and Collapse Columns
I would like to group colums the way the subtotal function groups rows. With a plus or minus sign to expand or collapse the group. How can I do that? Check your thread in .misc. JVICO wrote: > > I would like to group colums the way the subtotal function groups rows. With > a plus or minus sign to expand or collapse the group. How can I do that? -- Dave Peterson ...

help with entering data into Investment Accounts
Hello, I'm quite confused on how I should enter data into my Investment Account (a mutual fund account). Suppose I start from the beginning: I put $5000 into the account and buy some FundA and FundB. What kind of transaction are these, are they "buy" or "add shares"? Secondly, if I later transfer/switch from one fund to the other, are they "sell... buy" or "remove shares... add shares". (There isn't a "switch" transaction type). Finally, if I sell some funds and transfer the money out of the fund account, how do I do this? Use t...

Sumproduct a column where 2 adj text columns contain same value
Hi, I'm using a sumproduct formula to ascertain the number of times that a value between 0 and 1 occurs in column A (range A3:A26), where the adjacent cell in column B (B3:B26) contains either "Smith", "Draper" or "Jones": =SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"})) This formula works fine and details the number of entries where the value in column A is between 0 and 1, and the name in the adjacent column B cell is Smith Draper or Jones. What I need to do is the same SUMPRODUCT of 0-1...

Column format when merging Excel data to Word labels
I have a four column Excel spreadsheet which I have Mail Merged into Word to create labels. One of the columns in Excel contains a list of four digit numbers. Some of those numbers begin with a zero. When I have merged the data into Word labels, the four digit numbers starting with a zero have the zero missing. I have tried all sorts of number formats in Excel but can't get the zeros to stay there when merged to Word. Can anyone help???? I'm using Office 2003. There's an article on the Microsoft web site that might help you: Answer Box: Numbers don't merge right in Wo...

Force outlook to go to AD to get Exchange server name
I've this quesitons about Microsoft Outlook 2000/2003 clients (MAPI) for Exchange 2000 and 2003 servers. How can I force the Outlook clients to go to AD to find out which exchange server is holding the mailbox of the user? I want to do that every time clients starts or refresh it if it is already running. This way I don't have to reconfigure (or "Check Name") them whenever I move mailbox of the user from one exchange server to another in the organization. Thanks RD You shouldn't have to do that. When Outlook connects, it should get the Home Server attribute f...

Deleting excess data in a macro
Hi, I am downloading a report from Quickbooks that gives me transaction details such as Customer Name, Date, Item Number, Item Name, QTY and Total. Quickbooks then provides a subtotal for each customer. In this row, all of the other data in blank (there is not name, date, item number etc in this row. What I want to do is to create a macro that only has the transaction data in it and strips out the subtotals. What I have done is to sort the workbook by data which give me what I want. However, at the end of the spreadsheet is all of the subtotals by themselves with the blank data...

Ordering columns.
Dear all, I have the following problem: I have a column of data several thousands cells long with values (let's call that D1), indexed by another column (let's call that I1). I want to perform a division operation of the values in that column by the values in another column of values (call that D2), which is indexed in the same way by another column (let's call that I2). But I want to do this operation only for equivalent index codes (le's say, divide the vale indexed as I1=0001 in D1 by the value indexed as I2=0001 in D2). Nevertheless, the index is not necessarily co...

Changing Entity Name
We need to change the wording from "Account" to "Customer". can we do that? On Wed, 27 Jul 2005 02:15:03 -0700, Shawki <Shawki@discussions.microsoft.com> wrote: >We need to change the wording from "Account" to "Customer". can we do that? Not possible in CRM I'm afraid! You will be able to do this in the next version of CRM, 3.0. -- Matt Wittemann http://icu-mscrm.blogspot.com "Shawki" wrote: > We need to change the wording from "Account" to "Customer". can we do that? This is possible. Unsupported t...

How can I find the common names in two columns of names?
I have two columns of names. I want to find if there are any common names in these two columns, and if there are I want to figure out which ones are common. The only solution I could think of so far is using the find function in Excel by entering each name individually from the second column and searching them in the first column. It works, but considering the fact that I have more than 250 names in the second column and 990 names in the first one, I just wanted to know if there is a simpler way. I also have the SSN of these individuals as separate columns, in case I might need nume...

How do I sort inbox on two columns
A person can sort the inbox by (for example) clicking on the top of the date colun. After a few seconds the list is sorted by date. Click on the from column and after a few seconds the list is sorted by "from" but what if I would llike to sort by from within date or date within from, or any other combiation of two columns. How can I do that? K View-> Arrange By-> Custom...-> Group By: From-> Sort: received -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, A...

how to resize columns without affecting columns above?
How do i construct a table of columns adjusting the coulumn widths without affecting the graphs above? Right click over the chart, choose, Format Chart Properties. From the userform that pops up, choose the Properties dialog and select Don't move or size with cells. -- http://www.ExcelHelp.us ed@ExcelHelp.us 888-MY-ETHER ext. 01781474 "ggault" wrote: > How do i construct a table of columns adjusting the coulumn widths without > affecting the graphs above? ...