Forum Discussion
Please help me fill due date column.
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.
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.
