Helper Column and VLOOKUP
Hello,
I need help with a formula. Below is the requirement.
In the Project tab, Column E is the Helper Column. It should return the date from Column I that corresponds to that Task Number in Column B. In Column D, the user will define the preceding task it is dependent on. Accordingly the next workday of the Task in Column D should be auto-populated in Column G.
Any help will be much appreciated.
Regards,
Vijay
Did you mean
E4
=IFERROR(VLOOKUP(D4,$B$2:$I3,8,FALSE),0)
Regards.
My English is very poor, so please be patient >_<"
My Dynamic Related Dropdown post:
https://www.excelforum.com/excel-for...ml#post5657376
https://www.excelforum.com/excel-for...ml#post5655711
https://www.excelforum.com/excel-for...ml#post5655762
Thank you Chingmai,
But the formula did not work. Also, can you explain what us $13,8,FALSE) please?
Define "did not work".
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi AliGW, the formula did not return the desired date from Column I.
Thank you.
Please try in E3 and copy down:and in G3 and copy down:Formula:
Please Login or Register to view this content.Formula:Please Login or Register to view this content.
In G2 then copy down
=IFERROR(WORKDAY(INDEX($I$2:$I2,MATCH(D3,$B$2:$B2,0)),1,'Hol-Cal'!$C$2:$C$20),"")
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
What is expected date from column I ?
Regards.
Dear Hans,
Thank you for the formula. It worked exactly as the requirement. Thank you so much for the help, much obliged!
Regards,
Vijay
Dear Srinavasamurthy,
Thank you so much for the formula. I was surprised to see that it does not even use a helper column, that was really nice. But for some reason, the formula is not working in the lower cells. Sharing the file. Am sure you would be keen to see where's the gap, and so am I.
Regards,
Vijay
Hi ChingMai,
Column I is the end date of a task. Based on that end date, the dependent task must begin on the next workday. Thank you for your interest and willingness to help. I truly appreciate it.
Regards,
Vijay
Thanks for the feedback, I'm glad to have helped.
Please consider adding reputation to the answers of all helpers, whose answer you think deserves it.
Pl see file. It is working.
Nothing was filled in D3
Last edited by kvsrinivasamurthy; 11-22-2022 at 06:20 AM.
Thanks for the rep..
Hi Srinavasamurthy,
Thank you for the follow up. Yes, it is working after you have populated D3. Just want to point out an interesting fact though, in the formula written by Hans Douwe where he has made use of a helped column, even there is no dependent task, the formula works in the subsequent cells. With your formula, if I delete the dependency on any task, the formula stops working in the subsequent cells. Not pointing out any shortcomings as such, but bringing it to your attention as an expert, purely for academic reasons. Hope it helps because many people would be benefiting from your solutions. Thank you again.
Warm regards,
Vijay Jangam
If there is no dependent task how you decide the starting date of the present task?
Good question. Initially I had thought of inputting the start date manually. But then, that requires me to delete the formula in the cell. If I want to make it dependent on a previous task, I would need to copy the formula again in the cell. So the deleting and copying is not a very desirable option. I am now planning to protect that column so that we don't have to do that. In which case, your formula holds good. Thanks again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks