Question description:
This user has given permission to use the problem statement for this
blog.
I'm trying to make a gant chart but with non-linear predecessors. Trying to make a formula if a predecessor is a task no. the start date of one task will be the end date of the predecessor.
Can't figure out how to use the IF command for this
Solved by G. Y. in 19 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
05/10/2018 - 06:31
Hi…Welcome to Got It Pro. I see that your question is about fixing IF formulas.
Excelchat Expert
05/10/2018 - 06:31
I can help you with that problem.
Excelchat Expert
05/10/2018 - 06:31
Before we get started, this is a quick reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
User
05/10/2018 - 06:31
Hi! Thanks and no problem!
User
05/10/2018 - 06:32
will i fire together something to show the problem
Excelchat Expert
05/10/2018 - 06:32
Could you please share your file or a sample data in this preview sheet will be fine as well?
User
05/10/2018 - 06:33
yep give me 2 seconds
Excelchat Expert
05/10/2018 - 06:33
Sure
User
05/10/2018 - 06:34
my problem is i am trying to write a formula
User
05/10/2018 - 06:34
that the start date of each specific task is = to the finish date of another one
Excelchat Expert
05/10/2018 - 06:35
Yep.. let me help you build the IF formula.
Excelchat Expert
05/10/2018 - 06:35
This will take me few minutes.
User
05/10/2018 - 06:35
ill keep plugging away
Excelchat Expert
05/10/2018 - 06:35
sure
User
05/10/2018 - 06:35
so far ive been using
User
05/10/2018 - 06:35
IF AND and MATCH to try and make it work
Excelchat Expert
05/10/2018 - 06:36
So basic rule is finish date of prior task must be start date for the following task, is that right?
User
05/10/2018 - 06:37
yep
User
05/10/2018 - 06:37
i can't use a simple reorder and make it previous cell as the overall sheet will be used by a group
Excelchat Expert
05/10/2018 - 06:37
I didn't get you.
User
05/10/2018 - 06:38
so normally I could just rearrange the tasks, to make it a simple formula
User
05/10/2018 - 06:38
but they will always be shuffled
Excelchat Expert
05/10/2018 - 06:38
So do you mean to say you are going to use predecessors as the identifier of the prior task?
User
05/10/2018 - 06:39
yep
Excelchat Expert
05/10/2018 - 06:39
I mean you will have that Column E to identify what was the prior task.
User
05/10/2018 - 06:39
or the identifier or the start date
Excelchat Expert
05/10/2018 - 06:40
Ok...let me build a formula and let's see if that works.
User
05/10/2018 - 06:40
so column B will depend on the finish date of the predecessor which is labelled in column D and given by column E
Excelchat Expert
05/10/2018 - 06:44
Could you please review the formula in B4 and down?
Excelchat Expert
05/10/2018 - 06:45
Does that work?
User
05/10/2018 - 06:45
could you explain it please
Excelchat Expert
05/10/2018 - 06:45
Sure
Excelchat Expert
05/10/2018 - 06:45
I am using Index-Match function.
Excelchat Expert
05/10/2018 - 06:46
So the formula syntax is = Index(Table to index,row number, column index)
Excelchat Expert
05/10/2018 - 06:46
So I am using table as column A to E
Excelchat Expert
05/10/2018 - 06:46
And for row number, I am using Match function
Excelchat Expert
05/10/2018 - 06:47
So Match(value to match,lookup range,exact match)
Excelchat Expert
05/10/2018 - 06:47
Here value to lookup is the value in column E in the corresponding row.
Excelchat Expert
05/10/2018 - 06:48
lookup range is column A
Excelchat Expert
05/10/2018 - 06:48
0 is to indicate to return exact match
Excelchat Expert
05/10/2018 - 06:48
Then 4 is the column index from where we want the result.
User
05/10/2018 - 06:49
I understand
User
05/10/2018 - 06:49
thats pretty awesome
Excelchat Expert
05/10/2018 - 06:49
Since our table is from column A to E, and result that we want is from D, its a 4
User
05/10/2018 - 06:49
Thank you!
Excelchat Expert
05/10/2018 - 06:49
Perfect.
Excelchat Expert
05/10/2018 - 06:49
Is there anything else I can help you with this query?
User
05/10/2018 - 06:50
im doing an advanced course in uni and wasn't aware of the index formula. I'll look up how to use it in greater detail
User
05/10/2018 - 06:50
thank you again for your help!
Excelchat Expert
05/10/2018 - 06:50
Index is very helpful and more flexible then vlookup
Excelchat Expert
05/10/2018 - 06:50
Please feel free to leave any comments or feedback.
Excelchat Expert
05/10/2018 - 06:50
Thanks for your time. Please do come back for any new question. You can now end this session. Have a great day ahead!
User
05/10/2018 - 06:51
Ill leave some feedback! same to you!
