Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross-referencing dates in Excel gantt chart
Hi,
Using the workday function, a series of if statements and conditional formatting Ive created a Gantt chart in Excel that pretty much behaves like a Gantt chart in Microsoft Excel. You fill in a table of dates, choose who is responsible for the task and the Gantt chart populates automatically. Pretty much the final thing I want to do with it is allow the person filling it out to choose the predecessor task. At the moment, each task starts a day after the task in the row above. Currently, the table of dates looks like this: Column A €“ Task number (1,2,3,4,5 etc.) Column B €“ Task name Column C €“ Task owner (determines conditional formatting of bar cells in Gantt chart) Column D €“ Task start date Column E €“ Task end date Column F €“ Task duration The start date of the first task is entered manually, after that start dates are the end date of previous task + 1 working day. This is mostly ok, but sometimes the dependency isnt on the previous task, it may on a task two or three lines up. What I want to do is insert a new column G, labelled €śPredecessor€ť. Instead of the start date being €śprevious task + 1 working day€ť the start date cell for each task would look at the task number in column G. It would match this to the corresponding value in column A (task number) and take the €śend date of specified task +1€ť as its start date. Is there any way I can do this or am i just expecting far too much of Excel? Thanks Karl |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross-referencing dates in Excel gantt chart
=INDEX(E:E,MATCH(G3,A:A,0))+1
-- __________________________________ HTH Bob "Karl" wrote in message ... Hi, Using the workday function, a series of if statements and conditional formatting I've created a Gantt chart in Excel that pretty much behaves like a Gantt chart in Microsoft Excel. You fill in a table of dates, choose who is responsible for the task and the Gantt chart populates automatically. Pretty much the final thing I want to do with it is allow the person filling it out to choose the predecessor task. At the moment, each task starts a day after the task in the row above. Currently, the table of dates looks like this: Column A - Task number (1,2,3,4,5 etc.) Column B - Task name Column C - Task owner (determines conditional formatting of bar cells in Gantt chart) Column D - Task start date Column E - Task end date Column F - Task duration The start date of the first task is entered manually, after that start dates are the end date of previous task + 1 working day. This is mostly ok, but sometimes the dependency isn't on the previous task, it may on a task two or three lines up. What I want to do is insert a new column G, labelled "Predecessor". Instead of the start date being "previous task + 1 working day" the start date cell for each task would look at the task number in column G. It would match this to the corresponding value in column A (task number) and take the "end date of specified task +1" as its start date. Is there any way I can do this or am i just expecting far too much of Excel? Thanks Karl |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross-referencing dates in Excel gantt chart
Sorry to be dense, but in which column does this formula go?
"Bob Phillips" wrote: =INDEX(E:E,MATCH(G3,A:A,0))+1 -- __________________________________ HTH Bob "Karl" wrote in message ... Hi, Using the workday function, a series of if statements and conditional formatting I've created a Gantt chart in Excel that pretty much behaves like a Gantt chart in Microsoft Excel. You fill in a table of dates, choose who is responsible for the task and the Gantt chart populates automatically. Pretty much the final thing I want to do with it is allow the person filling it out to choose the predecessor task. At the moment, each task starts a day after the task in the row above. Currently, the table of dates looks like this: Column A - Task number (1,2,3,4,5 etc.) Column B - Task name Column C - Task owner (determines conditional formatting of bar cells in Gantt chart) Column D - Task start date Column E - Task end date Column F - Task duration The start date of the first task is entered manually, after that start dates are the end date of previous task + 1 working day. This is mostly ok, but sometimes the dependency isn't on the previous task, it may on a task two or three lines up. What I want to do is insert a new column G, labelled "Predecessor". Instead of the start date being "previous task + 1 working day" the start date cell for each task would look at the task number in column G. It would match this to the corresponding value in column A (task number) and take the "end date of specified task +1" as its start date. Is there any way I can do this or am i just expecting far too much of Excel? Thanks Karl |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross-referencing dates in Excel gantt chart
Column G - as per your request?
"Karl" wrote: Sorry to be dense, but in which column does this formula go? "Bob Phillips" wrote: =INDEX(E:E,MATCH(G3,A:A,0))+1 -- __________________________________ HTH Bob "Karl" wrote in message ... Hi, Using the workday function, a series of if statements and conditional formatting I've created a Gantt chart in Excel that pretty much behaves like a Gantt chart in Microsoft Excel. You fill in a table of dates, choose who is responsible for the task and the Gantt chart populates automatically. Pretty much the final thing I want to do with it is allow the person filling it out to choose the predecessor task. At the moment, each task starts a day after the task in the row above. Currently, the table of dates looks like this: Column A - Task number (1,2,3,4,5 etc.) Column B - Task name Column C - Task owner (determines conditional formatting of bar cells in Gantt chart) Column D - Task start date Column E - Task end date Column F - Task duration The start date of the first task is entered manually, after that start dates are the end date of previous task + 1 working day. This is mostly ok, but sometimes the dependency isn't on the previous task, it may on a task two or three lines up. What I want to do is insert a new column G, labelled "Predecessor". Instead of the start date being "previous task + 1 working day" the start date cell for each task would look at the task number in column G. It would match this to the corresponding value in column A (task number) and take the "end date of specified task +1" as its start date. Is there any way I can do this or am i just expecting far too much of Excel? Thanks Karl |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross-referencing dates in Excel gantt chart
You were asking for a formula to calculate the start date of subsequent
rows, so column D. -- __________________________________ HTH Bob "Karl" wrote in message ... Sorry to be dense, but in which column does this formula go? "Bob Phillips" wrote: =INDEX(E:E,MATCH(G3,A:A,0))+1 -- __________________________________ HTH Bob "Karl" wrote in message ... Hi, Using the workday function, a series of if statements and conditional formatting I've created a Gantt chart in Excel that pretty much behaves like a Gantt chart in Microsoft Excel. You fill in a table of dates, choose who is responsible for the task and the Gantt chart populates automatically. Pretty much the final thing I want to do with it is allow the person filling it out to choose the predecessor task. At the moment, each task starts a day after the task in the row above. Currently, the table of dates looks like this: Column A - Task number (1,2,3,4,5 etc.) Column B - Task name Column C - Task owner (determines conditional formatting of bar cells in Gantt chart) Column D - Task start date Column E - Task end date Column F - Task duration The start date of the first task is entered manually, after that start dates are the end date of previous task + 1 working day. This is mostly ok, but sometimes the dependency isn't on the previous task, it may on a task two or three lines up. What I want to do is insert a new column G, labelled "Predecessor". Instead of the start date being "previous task + 1 working day" the start date cell for each task would look at the task number in column G. It would match this to the corresponding value in column A (task number) and take the "end date of specified task +1" as its start date. Is there any way I can do this or am i just expecting far too much of Excel? Thanks Karl |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Gantt chart in Excel 2003 | Charts and Charting in Excel | |||
Gantt chart in excel? | Excel Discussion (Misc queries) | |||
Cross referencing | Excel Worksheet Functions | |||
how do I create a gantt chart in excel | Charts and Charting in Excel | |||
gantt chart in excel | Charts and Charting in Excel |