Convert list of addresses

Patrick2788 

This is what I want to do. I only started this sheet yesterday and I am just testing functions at this point.

I am creating a "Critical Path" spreadsheet. I do have a lot of experience with CPM and a lot of, but dated, experience with EXCEL. Nothing out there does what I want, or is corporate based, extensive and way too expensive for what I need to do.

In CPM, or more accurately in this case, "Precedence Method" all work on a project is divided into definable "activities" that are linked in a logical chain and network fashion. No activity can start until all preceding linked activities have completed.

So, with a list of preceding activities and the date they each finish on, the start date of any following activity can be determined, and that start date is based on the completion date any of the preceding linked activities that have completed. (That's the basic theory, and it gets a bit more complex than that.)

That date is called the "Early Start". Add the duration the activity is expected to take, and you get the completion or "Early Finish" date, and that date feeds the next linked activity's early start date, which is the day after. If there is more than one preceding activity, the latest early finish date determines that early start date.

So, in my spreadsheet, I have a cluster of cells for each activity. They contain, among other things, the activities ID, which in my case is a cell reference, the text description, early start date, duration, early finish and a list of preceding activities.

It is the list of preceding activities and the early start that is the focus of this problem.

To keep it simple, the Activity ID is its Early Finish Cell address. I just want to simply type that list in without framing it as a displayed formula.

Like this, in cell, say D5, type in   A3,M7,V66,R3

Then in the Early Start cell, the basic formula would be  =MAX(0,D5)+1  which gives me my Early Start date.
I will probably need another cell to convert the list of Activity ID's to cell addresses.

Show me how to include here the spreadsheet, and I will post it.

jgb

Patrick2788 I agree and suggest as much also.

JG_Berson as for more simple formula it turns out INDIRECT might work on an array (it originally failed for me but I think that was because I had to add the TRIM).  So here is a more simple formula but still won't work for you:

=MAX(INDIRECT(TRIM(TEXTSPLIT(G1,","))))

but using the old trick to go from text to array may work for you but you will probably have to enter the formula using CTRL-SHIFT-ENTER:

=MAX(INDIRECT(TRIM(MID(SUBSTITUTE(G1,",",REPT(" ",LEN(G1))),(ROW($A$1:INDEX($A:$A,LEN(G1)-LEN(SUBSTITUTE(G1,",",""))+1))-1)*LEN(G1)+1,LEN(G1)))))

I'll cross my fingers for you.