analysing multiple references contained in one text string

[baitmaster]

greetings all

I need to link the start date of one task, to the final end date of any possible combination of previous tasks. Sadly, the precedent tasks will be identified only by a single cell entry containing the name of every relevent one

If only one precedent task, then the start date of the dependent would be simply vlookup (precedent reference)

But since there might be multiple precedents, all details contained within a a cell value of e.g. ("precedent1", "precedent2", etc), I need a function to read all of these in turn, and return the maximum end date of each

As a final pain in my @rse, this function will be in a larger model, containing a lot of statistical processing, hence I can't have a long-winded way of analysing this, as the requirement to recalculate after each iteration (expected 10,000) will slow the model down too much. This is an integral calculation, so cannot be switched off in any way

Preferred way forward would be to write a specific VBA function that effectively recreates VLOOKUP, or maybe INDIRECT or similar, but using multiple references contained in a single text string. Effectively Im trying to recreate certain functionality from MS Project, and convert into Excel for use in calculations. Alternative methods are invited!

Any help is much appreciated

 

[Andrew Poulsom]

To get help I think you will need to be more specific.

 

[Yard]

Can't understand how your data is constructed. Are you saying you have a list of precedent tasks, with end dates adjacent to them, and you want to find the latest date for any tasks which contain a certain word??

With a list of words in A1:A7 and dates in B1:B7 then you can pick out the latest date of any word containing a string in C1 by using:

=MAX(IF(ISNUMBER(SEARCH(C1,$A$1:$A$7)),$B$1:$B$7,0))

Am I anywhere near the right track?

 

[baitmaster]

ok, simplified version of problem...

4 tasks, T1 - T4

Start of T1 is say 2009. Duration is variable, say 2 years
Start of T2 is end date of T1. Cell in [row applicable to T2] tells the model this, as it contains the value "T1"
Start of T3 is also 2009, but end date is as yet unknown
Start of T4 is dependent on completion of T2 and T3, hence it must be the higher of the two end dates. Cell in [row applicable to T4] tells the model this, as it contains the value "T2, T3"

I need a function that can look at the cell containing "T2, T3", do a VLOOKUP on both T2 and T3, and return the higher of the two end dates

Hope this helps, it seems easier to explain when you are looking at the problem..

 

[baitmaster]

Yard - possibly, but the string in C1 will be any combination of the specific words in A1:A7, and I need the highest (single) date of all that are listed in C1, if that makes sense?!

 

[baitmaster]

Hmmm, I figured this might be tricky

I think I will write a routine that looks at the precedents list in turn, and rewrites specific formulae for each line, depending on the criteria, i.e. whether cell is (a) blank, (b) contains "," within text, or (c) neither

Thanks for looking anyway