I posted yesterday, (don't know how to reference post), and Wayne was nice enough to help me out. However, last night I realized I failed to specify exactly what info I wanted to retain in the worksheet. This time I've taken his suggestion and pasted an example workbook.
What I'm wanting to do it to track tasks in one worksheet by the number of days a task takes versus the original estimate.
The issue is that I want to allow the user the option of entered EITHER an estimated END DATE for a task, OR an estimated NUMBER of DAYS the task will take. Wayne came up with a solution, but I had him using COLUMN A for the TASK, a drop down in COLUMN B to choose which you wanted to enter, and then in the next COLUMN entering the info.
So in Column C, you may have END DATE or NUMBER of DAYS.
But what I really need is to enter the END DATE in COLUMN C OR the NUM of DAYS in COLUMN D... and then the other COLUMN would calculate the appropriate DATE or Num of Days...
That way, for each task, I'm showing a START DATE in B; an END DATE in C, and a NUMBER OF DAYS in D....
It is just that you enter either DATE or DAYS and the other one automatically populates.
Several problems:
1). How do I enter a formula in a cell that will calculate OR just let you manually enter data?
2). I was also trying to use conditional formatting to turn the cells you manually enter data into GREEN, and those you let CALCULATE turn red (I almost got this one with Wayne's help);
3). Once I do manually enter data into the cell, the formula is gone... what if I then decide that instead of days I want to enter date?
EXAMPLE... A start date is June 1. I choose to enter 10 days. When the END DATE is calculated, I notice it is the first day of my vacation... so I decide I just will enter the END DATE... but since I previously entered 10 days, the formula to calculate the number of days is gone... I can enter the day I come back from vacation, but it no longer will calculate the number of days, since I erased the formula when I originally put in 10 days! (I was trying to do some sort of reset button that would put the formulas back in... but didn't know how the heck to start that! --- was thinking maybe once the "other" data is calculated -- either the END DATE or NUM of DAYS -- the cells all turn red... and if you want to change something you have to click a reset button and start over????? - Goofy way to do this I guess?
Anyway, hopefully this makes since to one of you guys. I'm attaching the example workbook.
I tried to use xl2bb to create a worksheet... hopefully this works????
What I'm wanting to do it to track tasks in one worksheet by the number of days a task takes versus the original estimate.
The issue is that I want to allow the user the option of entered EITHER an estimated END DATE for a task, OR an estimated NUMBER of DAYS the task will take. Wayne came up with a solution, but I had him using COLUMN A for the TASK, a drop down in COLUMN B to choose which you wanted to enter, and then in the next COLUMN entering the info.
So in Column C, you may have END DATE or NUMBER of DAYS.
But what I really need is to enter the END DATE in COLUMN C OR the NUM of DAYS in COLUMN D... and then the other COLUMN would calculate the appropriate DATE or Num of Days...
That way, for each task, I'm showing a START DATE in B; an END DATE in C, and a NUMBER OF DAYS in D....
It is just that you enter either DATE or DAYS and the other one automatically populates.
Several problems:
1). How do I enter a formula in a cell that will calculate OR just let you manually enter data?
2). I was also trying to use conditional formatting to turn the cells you manually enter data into GREEN, and those you let CALCULATE turn red (I almost got this one with Wayne's help);
3). Once I do manually enter data into the cell, the formula is gone... what if I then decide that instead of days I want to enter date?
EXAMPLE... A start date is June 1. I choose to enter 10 days. When the END DATE is calculated, I notice it is the first day of my vacation... so I decide I just will enter the END DATE... but since I previously entered 10 days, the formula to calculate the number of days is gone... I can enter the day I come back from vacation, but it no longer will calculate the number of days, since I erased the formula when I originally put in 10 days! (I was trying to do some sort of reset button that would put the formulas back in... but didn't know how the heck to start that! --- was thinking maybe once the "other" data is calculated -- either the END DATE or NUM of DAYS -- the cells all turn red... and if you want to change something you have to click a reset button and start over????? - Goofy way to do this I guess?
Anyway, hopefully this makes since to one of you guys. I'm attaching the example workbook.
I tried to use xl2bb to create a worksheet... hopefully this works????
| Book3 | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 1 | START DATE | Enter Data In Green Box ONLY | Est END DATE | Est NUM OF DAYS | ACTUAL END DATE | Number of Days OVER/UNDER | |||||||
| 2 | Determine Budget | 6/1/23 | NUM of DAYS | 6/8/23 | 5 | 6/5/23 | -3 | ||||||
| 3 | Pick Vendor | 6/15/23 | END DATE | 6/20/23 | 4 | 6/21/23 | 1 | ||||||
| 4 | Place Order | 6/15/23 | |||||||||||
| 5 | |||||||||||||
| 6 | HOW DO I DO CONDITIONAL FORMATTING SO A THE CELL TURNS | ||||||||||||
| 7 | RED OR GREEN BASED ON COLUMN D? | ||||||||||||
| 8 | |||||||||||||
| 9 | |||||||||||||
Sheet1 | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2 | E2 | =IF($D2="Num of Days", WORKDAY($C2,$F2)) |
| F3 | F3 | =IF($D3="end date",NETWORKDAYS($C3,$E3)) |
| H2:H3 | H2 | =(NETWORKDAYS($C2,$G2)-(NETWORKDAYS($C2,$E2))) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| E3 | Other Type | Color scale | NO | |
| F2 | Expression | =$D2="Num of Days" | text | NO |
| E2 | Expression | =$D2="Num of Days" | text | NO |
