new to excel but have a complicated question

[sthaker]

Hi there,

I have created a project spreadsheet with a number of tasks and a number of due dates for these tasks...

I'm finding it a pain to have to go in and change all the dates by one day manually when a date for a particular task changes... is there a way to update the dates for the other tasks automatically?

In most cases, I have to update the date by one day, but there are many tasks, so it takes me ages..

hope someone can help....

S

 

[arthurbr]

Welcome to the board
This a a possibility to increase dates with one day :

Enter 1 in a cell somewhere
Select the cell
Right click
select "Copy"
select the cells you want to increase by one day
Right click
Select Paste special
Select "Add"
OK


BTW you posted twice, don't forget to delet the second post !

 

[sthaker]

I'll give that ago thanx...

 

[sthaker]

hmmm, the way I have created the spreadsheet, I have listed the dates across the top and I color in the cell areas as you would in like a gant chart....

so ultimately I need to move the coloured cells on by one day...

 

[crackjack]

so ultimately I need to move the coloured cells on by one day...

Not quite. Excel has a wonderful feature called conditional formatting that you can use to change the background colors automatically.

If you provide me a little more detail on exactly what you are trying to achieve, I can provide more specifics, or even a step-by-step.

 

[sthaker]

Hey, cool... I will try my best to explain...

Basically the first column lists a project name and the various tasks for this project underneath. So col A lists the Project Names and Tasks underneath.

cells b right through to col AM ldentifies each month each day of the week... underneath this I use the highlighter to mark the due dates for each task in each cell for my project. So it looks like a gant chart....

however, when I have a date change, I have to move all the highlighted cells along one by one to the next day... which takes me a long time to do... too much cutting and pasting to the next cell.... especially when I have over 100 tasks listed.

 

[crackjack]

Ok. So it looks something like this:

A--- B-- C-- D--
---- W1M W1T W1W
Pro
Tsk1 (highlight)

Yes?
So you are not really putting any numbers in the cells? Except for the dates in row 1, that is.

 

[Kevlarhead]

You need this

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343

Jon Peltier on Gantt Charts. A nice introduction to making them, along with a link to a stacks of webpages giving more information.

 

[crackjack]

If so, what you'd need to do is:
1. use 2 more columns --->
a. col B - either begin date or dependency
b. col C - effort required (day)
2. click on D2
2. go to menu option Format > Conditional Formatting
3. put condition as if D$1 is greater than or equal to $B2 AND D$1 is less than or equal to $B2+$C2, then format --> background color is whatever color you want
4. Copy this cell
5. Paste special (format) across the spreadsheet

This way, when a date changes, all you'll need to do is type in the new beginning date or effort in col B or col C

 

[sthaker]

Hey that's great, thanks guys for your help, I will try it out and let you know how I get on...

muchly appreciated