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...
Check structure
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Check structure
You do not have the required permissions to view the files attached to this post.
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Check structure
as an additition here is a picture of how it shoul work (for this example)
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check structure
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.
Please try to explain in clear and unambiguous language what you want.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Check structure
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
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
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check structure
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.
I'm sure it is crystal clear to you, but you will have to explain it to us.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Check structure
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
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
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check structure
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",""))))
=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",""))))
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Check structure
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....
the formula works well till row 29; then i get the error #NAME?... i wonder, what happens there....
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check structure
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Check structure
ok, i figured it out, there was a english - german problem in my office....
Many thanks, Hans
stfan
Many thanks, Hans
stfan