Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I read a table and taken action?
How can I do this with or without Macro/VB code?
I want to create a Gantt Chart like graphic by coloring a grid (columns 1-52 weeks, rows dictated by number of Projects). The Design task would be colored red, Install Hardware blue etc. Each project would have 1 row (only) where Design, Install Hardware etc would be colored in the appropriate cells on the grid (Project1 Design task would show in 26th cell in the row, Install Hardware 37th thru 40th) Note I will create the data from MS Project, but I need to present it succinctly in Excel for my client. ProjectID TaskID Start Week Duration Project1 Design 26 1 Project1 Install Hardware 37 4 Project1 Install Software 41 4 Project1 Configure 49 1 Project1 Prep 50 2 Project1 Support 52 1 Project2 Design 2 1 Project2 Install Hardware 15 2 Project2 Install Software 17 3 Project2 Configure 20 1 Project2 Prep 21 1 Project2 Support 22 4 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I read a table and taken action?
You could do it by selecting the appropriate cells and format-cells-pattern
you could also do by by making a floating bar chart Microsoft has several templates for excel gantt charts http://search.microsoft.com/results....n-US&FORM=SSME "MSPLearner" wrote: How can I do this with or without Macro/VB code? I want to create a Gantt Chart like graphic by coloring a grid (columns 1-52 weeks, rows dictated by number of Projects). The Design task would be colored red, Install Hardware blue etc. Each project would have 1 row (only) where Design, Install Hardware etc would be colored in the appropriate cells on the grid (Project1 Design task would show in 26th cell in the row, Install Hardware 37th thru 40th) Note I will create the data from MS Project, but I need to present it succinctly in Excel for my client. ProjectID TaskID Start Week Duration Project1 Design 26 1 Project1 Install Hardware 37 4 Project1 Install Software 41 4 Project1 Configure 49 1 Project1 Prep 50 2 Project1 Support 52 1 Project2 Design 2 1 Project2 Install Hardware 15 2 Project2 Install Software 17 3 Project2 Configure 20 1 Project2 Prep 21 1 Project2 Support 22 4 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I read a table and taken action?
Thanks for your response. I appreciate your help.
I did check the MS Excel example your referenced. It does not quite do the job. I need to show all Project tasks in a single row . For the sample data below, the result would appear in 2 rows. I don't believe the referenced example can do this - but I am ready to be convinced otherwise. Thanks again. "MSPLearner" wrote: How can I do this with or without Macro/VB code? I want to create a Gantt Chart like graphic by coloring a grid (columns 1-52 weeks, rows dictated by number of Projects). The Design task would be colored red, Install Hardware blue etc. Each project would have 1 row (only) where Design, Install Hardware etc would be colored in the appropriate cells on the grid (Project1 Design task would show in 26th cell in the row, Install Hardware 37th thru 40th) Note I will create the data from MS Project, but I need to present it succinctly in Excel for my client. ProjectID TaskID Start Week Duration Project1 Design 26 1 Project1 Install Hardware 37 4 Project1 Install Software 41 4 Project1 Configure 49 1 Project1 Prep 50 2 Project1 Support 52 1 Project2 Design 2 1 Project2 Install Hardware 15 2 Project2 Install Software 17 3 Project2 Configure 20 1 Project2 Prep 21 1 Project2 Support 22 4 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I read a table and taken action?
You can't use a formula to change a format (colour), so to do what you
want would require a macro. However, if you dispense with the colours and use single letters to denote the tasks (i.e. D for Design, H for Hardware, S for Software, C for Configure, P for Prep and A for Help {Assistance} ), you could achieve a similar effect in one cell with this formula: =REPT(" ",B2-1)&REPT("D",C2)&REPT(" ",B3-B2-C2)&REPT("H",C3)&REPT(" ",B4-B3-C3)&REPT("S",C4)&REPT(" ",B5-B4-C4)&REPT("C",C5)&REPT(" ",B6- B5-C5)&REPT("P",C6)&REPT(" ",B7-B6-C6)&REPT("A",C7) I put your tasks in A2 to A7, with start week in column B and Duration in column C. Here's the result: D HHHHSSSS CPPA You must use a non-proportional font such as Courier New to see the effect properly, and it might help to put this formula in the cell below: =REPT("1234567890",5)&"12" to help align the weeks (also with a Courier Font). Alternatively, use "." instead of " " in the first formula. Not exactly what you wanted, but a similar visible presentation of the data. Hope this helps. Pete On May 18, 10:09 pm, MSPLearner wrote: Thanks for your response. I appreciate your help. I did check the MS Excel example your referenced. It does not quite do the job. I need to show all Project tasks in a single row . For the sample data below, the result would appear in 2 rows. I don't believe the referenced example can do this - but I am ready to be convinced otherwise. Thanks again. "MSPLearner" wrote: How can I do this with or without Macro/VB code? I want to create a Gantt Chart like graphic by coloring a grid (columns 1-52 weeks, rows dictated by number of Projects). The Design task would be colored red, Install Hardware blue etc. Each project would have 1 row (only) where Design, Install Hardware etc would be colored in the appropriate cells on the grid (Project1 Design task would show in 26th cell in the row, Install Hardware 37th thru 40th) Note I will create the data from MS Project, but I need to present it succinctly in Excel for my client. ProjectID TaskID Start Week Duration Project1 Design 26 1 Project1 Install Hardware 37 4 Project1 Install Software 41 4 Project1 Configure 49 1 Project1 Prep 50 2 Project1 Support 52 1 Project2 Design 2 1 Project2 Install Hardware 15 2 Project2 Install Software 17 3 Project2 Configure 20 1 Project2 Prep 21 1 Project2 Support 22 4- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Interpolate, Interpolation, VlookUp, HlookUp, Read a table | Excel Worksheet Functions | |||
Unable to read error in a pivot table | Excel Discussion (Misc queries) | |||
How can a file be converted from Read-Only to Read/Write | Excel Discussion (Misc queries) | |||
Excel-read a table | Excel Worksheet Functions | |||
Sumproduct in Excel Spreadsheet to read Access db table | Excel Worksheet Functions |