Check structure

[Stefan_Sand]

Hi,

I have a workbook, where i have a given structure of tasks; how do i have to make a formula in column e to get x-es corresponding to the given structure;
showing as many x-s as are given by structure; please see the attached file.
I have 3 levels for showing - 1st, 2nd, 3rd how many activities, Tasks and subtasks (given names).
I only want to see an x, if the requirements fit....
thanks in advance...

[Stefan_Sand]

as an additition here is a picture of how it shoul work (for this example)

[HansV]

I don't understand your example - why do you have x-es for 10 subtasks while Subtasks = 2 but x-es for 2 tasks while Tasks = 2?

Please try to explain in clear and unambiguous language what you want.

[Stefan_Sand]

hi Hans,

i only want to mark the activities, tasks and subtasks that are valid through inserted numbers - > how many activities are used, how manytasks and how many subtasks and which one meets the requirements.

for example, i have 2 tasks and 5 subtasks, which should give me the right numbers - tasks 501, 601 - subtasks 5001 to 5005 and 6001 to 6005 ....

The picture should show the solution for the given numbers in the example in the cells D4:D6 - > regarding to these numbers there should appear an x in column e.

i marked all the relevant tasks (activities, subtasks...) in column e with an x , because there should be the formula which gives me an x, if the case from cells D4:D6 is met.

Stefan

[HansV]

But what does "meet the requirements" mean?
I'm sure it is crystal clear to you, but you will have to explain it to us.

[Stefan_Sand]

ok, lets explain in detail:

i have activities with numbers 1 to 9, tasks 101, 201, ... 901 and subtasks with 1001, 1002, ... 1009, 2001, .. 2009 , all to 9009
the crux is that i want get out of this matrix, which task subtask meets the given number -> like i have 2 tasks, then there should be taken the "first" given tasknumbers = 501 and 601 -> from the matrix 1,2,3,4,5,6,7,8,9 corresponding to activities -> 5,6,4,7,3,8,2,9,1 and the subtasks 5001, 6001, 4001, 7001, 3001, 8001, 2001, 9001, 1001... and so on; do you understand now, why i sent the picture?

Stefan

[HansV]

Try this formula in E11, then fill down:

=IF(LEN(D11)=1,IF(MATCH(D11,$I$18:$I$26,0)<=Activities,"x",""),IF(LEN(D11)=3,IF(MATCH(D11,$J$18:$J$26,0)<=Tasks,"x",""),IF(LEN(D11)=4,IF(AND(MATCH(INT(D11/1000),$I$18:$I$26,0)<=Tasks,MATCH(MOD(D11,10),$K$17:$S$17,0)<=Subtasks),"x",""))))

[Stefan_Sand]

hi Hans, thanks for your quik reply;
the formula works well till row 29; then i get the error #NAME?... i wonder, what happens there....

[HansV]

It works OK for me in the rows below too - see the attached version. The formulas are in column F so that you can compare the result to the hand-filled values in column E.

WBS Check.xls

[Stefan_Sand]

ok, i figured it out, there was a english - german problem in my office....

Many thanks, Hans

stfan