Excel wizardry challenge

[adolf hamster]

ok, so i have a sheet that i'm using for some project planning, it has a list of tasks and each one has a column for which task has it's parent and children.

what i'm wanting to do is auto-fill the parents column, so say we have the following:

Task number task parents task children
1 4,2
2 1 4
3 4
4 1,2,3

so basically what i want is to be able to search the cells in the task children for references to the task number and return their corresponding task numbers in the parents column.

the tricky thing is the standard index/match will only return the first reference to it, but i need to return multiple references to a single cell.

if it helps the syntax of the children column can be changed to whatever format is needed, i'm thinking comma seperated might make it easiest?

and yes i know i should be using project for this, but damn the licenses are expensive.

 

[CaptainComedy]

Microsoft access?

isn't what you are doing a lookup?

I don't think you are looking at it right, this would be many to many because of the child references, if it was showing parents only on task items, fair enough, but many to many requires multiple rows, you don't want to be going horizontal for what is essentially a tree list, it's not scaleable.

so either don't include children on row, or allow multiple rows.

Option 1 - no children

ID_| Task__| Parent
_1_| task 1 | 0 <- Prime
_2_| task 2 | 1 <- child of 1
_3_| task 3 | 2 <- child of 2
_4_| task 4 | 2 <- child of 2
_5_| task 5 | 1 <- child of 1


Option 2
Task Table
ID_| Task__|
_1_| task 1 |
_2_| task 2 |
_3_| task 3 |
_4_| task 4 |
_5_| task 5 |

Reference Table
ID | Parent | Child
_1| 1_____|2
_2| 1_____|5
_3| 2_____|3
_4| 2_____|4

This would be my preference and in a DB tbh (even access)

 

[adolf hamster]

bah, typical, it didn't put in the spaces for that example

Task number task parents task children
1________________________4,2
2_______________1________4
3________________________4
4_____________1,2,3

i did find a macro based solution that works, but wont handle more than 1 value for children,so in the above example it would look thus:

Task number task parents task children
1________________________4,2
2________________________4
3________________________4
4______________2,3

unfortunately i dont have access, or even better project, which are really the right tools for this job so i'm having to make do with excel.

the idea is this table when fleshed out with start/end dates, task times etc is going to be a pseudo gantt chart but due to the size of the project i was wanting to automate some of these bits as it'll be a right pain to do manually.

 

[Vonhelmet]

Do you need both task parents and task children? It seems these are always going to be the inverse of each other, at least at some level. Couldn’t you just set the task parents for each task? That might simplify things.

 

[adolf hamster]

Do you need both task parents and task children? It seems these are always going to be the inverse of each other, at least at some level. Couldn’t you just set the task parents for each task? That might simplify things.

it makes it easier to find what you need in either direction, i could do the parents/children manually but i live by the code of if in doubt automate.

 

[Delvis]

Can you not just do something like a conditional format?

 

[Vonhelmet]

it makes it easier to find what you need in either direction, i could do the parents/children manually but i live by the code of if in doubt automate.

Ah, ok, sorry that’s where I was headed anyway. Teach me to read the post properly...

So you want to parse the parent list of each task and update the child fied, or whichever way round, yes?

I think you will need a macro, because it’s an iterative process. For each parent task you need to look at an unspecified number of tasks in a list and generate an array of tasks with that task given as the parent and then spit that array into the child field. I suppose you could do it with formulae with concatenation and matching, but you’d end up with very long formulae (is there a cap on length?) and they’d all need updating every time a task was added.

The cap on formula length is 8,192 characters, so you’re unlikely to hit that, but even so.

 

[Tuppy_Glossop]

You could use a worksheet for each task.

 

[Kenai]

If this is relatively short term usage, I'm sure they do a fairly reasonable subscription fee for Project. £22pm with a free trial.

If it's longer term, there are various alternatives, some free, some open source etc. that will probably be easier than trying to strongarm Excel into performing like a crippled version of Project with macros.

 

[adolf hamster]

Ah, ok, sorry that’s where I was headed anyway. Teach me to read the post properly...

So you want to parse the parent list of each task and update the child fied, or whichever way round, yes?

I think you will need a macro, because it’s an iterative process. For each parent task you need to look at an unspecified number of tasks in a list and generate an array of tasks with that task given as the parent and then spit that array into the child field. I suppose you could do it with formulae with concatenation and matching, but you’d end up with very long formulae (is there a cap on length?) and they’d all need updating every time a task was added.

The cap on formula length is 8,192 characters, so you’re unlikely to hit that, but even so.

indeed, the problem is when the child field has seperated values that'd need split up, although so far the one ive found online seems to work ok as long as each process has only 1 child.