Forum Discussion
Please help me fill due date column.
Any task should end in 7 days, incident in 15 days, and project in 30 days from its start date. How to fill due to dynamically as I have 1000 rows.
Task | Start date | Due Date |
Incident | 10/31/20 |
|
Project | 10/31/20 |
|
Task | 10/31/20 |
|
Task | 10/31/20 |
|
Project | 10/31/20 |
|
Project | 10/31/20 |
|
Task | 10/31/20 |
|
Incident | 10/31/20 |
|
Project | 10/31/20 |
|
Please help!
Since you are looking for a dynamic method, therefore I would like to suggest below shown method to be followed.
How it works:
- Press Ctrl+F3 to get Range Name dialogue.
2. Enter an appropriate name in Name box.
3. Use this formula for references.
=OFFSET(Sheet2!$J$25, 0, 0, COUNTA(Sheet2!$J:$J), 1)Note:
- Purpose of doing this, if in future you need to add more Task along with days to add then no need to modify the cell references in the formula.
- Repeat step 1 to 3 for "Days to add column", and use this formula.
=OFFSET(Sheet2!$K$25, 0, 0, COUNTA(Sheet2!$K:$K), 1) Note: Name given to cloumn is ValueList.- Now enter this formula in cell I25 and fill down.
=IF(ISBLANK(G25),"",H25+VLOOKUP(G25,CHOOSE({1,2}, TaskList, ValueList),2,FALSE))Adjust cell references in the formula along with Task List & related Days to add, as needed.
**** If this works for you then, you may mark it as best answer as well Like.
8 Replies
- Glad to help you JayJoshi ,, keep asking ☺
Since you are looking for a dynamic method, therefore I would like to suggest below shown method to be followed.
How it works:
- Press Ctrl+F3 to get Range Name dialogue.
2. Enter an appropriate name in Name box.
3. Use this formula for references.
=OFFSET(Sheet2!$J$25, 0, 0, COUNTA(Sheet2!$J:$J), 1)Note:
- Purpose of doing this, if in future you need to add more Task along with days to add then no need to modify the cell references in the formula.
- Repeat step 1 to 3 for "Days to add column", and use this formula.
=OFFSET(Sheet2!$K$25, 0, 0, COUNTA(Sheet2!$K:$K), 1) Note: Name given to cloumn is ValueList.- Now enter this formula in cell I25 and fill down.
=IF(ISBLANK(G25),"",H25+VLOOKUP(G25,CHOOSE({1,2}, TaskList, ValueList),2,FALSE))Adjust cell references in the formula along with Task List & related Days to add, as needed.
**** If this works for you then, you may mark it as best answer as well Like.
Rajesh_Sinha Rather complicated, IMHO. Why not use structured tables instead? Based on my original post, see attached.
- Not a bad idea, I do feel using Tale is an old practice, better show something different to OP and community, so that I've used Dynamic Range Name method, and CHOOSE with Lookup.
JayJoshi Easiest would be to create a small table with the parameters for each activity and VLOOKUP the number of days to be added. You may use XLOOKUP if you are recent version of Excel. The picture below demonstrates VLOOKUP. Enter the formula in C2 and copy it down as far as needed.
Note that the dates in column B have to be real dates. Not just texts looking like dates.
Riny_van_Eekelen Thank you so much. I am new to excel and would love to as much as I can. I appreciate your help. Thanks a lot.
Can I create a Dashboard for analysis in excel?
JayJoshi You're welcome!
Excel is capable of doing many things, including dashboards. Though, that is not something I specialise in. I suggest you come back here with a new question, once you are able to explain what kind of summary/analysis/reports/graphs you want to create on whatever data you are working with.
