none
Custom column formula RRS feed

  • Question

  • Hi,

    I am trying to create a formula which populates a custom task column dependant on interrogation of the tasks assigned resource name. I have a formula which works to a degree but not quite as I wanted originally (pasted it below). Is there a way to give a command that says if resource name "contains" xyz then column x = abc.

    Ideally the formula would (unlike the one below) be able to return more than just one answer per task if 2 or more resources were assigned.

    FYI the idea is to return a task test location, & luckily for us we decided at the outset to pre-fix all reource names with a code for there location i.e 'TCUK_resource name abc" = Shirley Uk.

     

    IIf(Left([Resource Names],4)='TCUK','Shirley',IIf(Left([Resource Names],3)='GTC','Gardone',IIf(Left([Resource Names],3)='TCW','Washington',IIf(Left([Resource Names],3)='PMP','Pamplona',IIf(Left([Resource Names],4)='TSCD','Dačice',IIf(Left([Resource Names],3)='TCD','Düsseldorf',IIf(Left([Resource Names],3)='TCS','Shanghai',IIf(Left([Resource Names],3)='EXT','External',''))))))))

     

    Regards

    Steve

    Tuesday, October 2, 2012 8:48 AM

Answers

  • Julie, Roopkumar,

    It seems that the answer was much more simple than we imagined, the following formula works exactly as i wished not matter how many resources are assigned to a task in any order.

    IIf(InStr([Resource Names],'TCUK')>0,'Shirley, ','') & IIf(InStr([Resource Names],'TCS')>0,'Shanghai, ','') & IIf(InStr([Resource Names],'GTC')>0,'Gardone, ','') & IIf(InStr([Resource Names],'TCW')>0,'Washington, ','') & IIf(InStr([Resource Names],'PMP')>0,'Pamplona, ','') & IIf(InStr([Resource Names],'TSCD')>0,'Dačice, ','') & IIf(InStr([Resource Names],'TCD')>0,'Düsseldorf, ','') & IIf(InStr([Resource Names],'EXT')>0,'External, ','')

    Thanks again for your help.

    Regards

    Steve

    • Marked as answer by Steve.D.H Wednesday, October 3, 2012 9:54 AM
    Wednesday, October 3, 2012 9:43 AM

All replies

  • Hi Steve,

    The Instr function returns the position of a string within another string.  So, if you have more than one resource assigned, it would work just fine.

    For example:

    IIf(InStr([Resource Names],"Julie")>0,"Julie","No")

    If I have the following assignments:

    Task 1 Julie

    Task 2 Julie, Steve

    Task 3 Bob

    The formula returns:

    Task 1 "Julie"

    Task 2  "Julie"

    Task 3 "No"

    I hope this helps.

    Julie

    • Marked as answer by Steve.D.H Tuesday, October 2, 2012 10:11 AM
    • Unmarked as answer by Steve.D.H Wednesday, October 3, 2012 9:54 AM
    Tuesday, October 2, 2012 9:56 AM
    Moderator
  • Hi Julie,

    Thanks, that works well thankyou. However is it possible to get the return, " Julie, Steve" for Task2 in your example above? That would be ideal for us if it was possible?

    For example i created the following formula " IIf(InStr([Resource Names],'TCUK')>0,'Shirley',IIf(InStr([Resource Names],'TCD')>0,'Dusseldorf','')) " and was hoping a task with 2 assigned resources, one containing text "TCUK" & the other containing text "TCD" would return "Shirley & Dusseldorf" in the same cell.

    kind regards

    Steve


    • Edited by Steve.D.H Tuesday, October 2, 2012 10:20 AM
    Tuesday, October 2, 2012 10:18 AM
  • Hi Steve,

    Add the formula mentioned below in Text1, Text2 or any other Text custom field:

    switch(Left([Resource Names],4)='TCUK' And instr([Resource Names],',')>0,'Shirley' & ' and ' & right([Resource Names],(len([Resource Names])-instr(6,[Resource Names],'_'))),Left([Resource Names],3)='GTC' And instr([Resource Names],',')>0,'Gardone' & ' and ' & right([Resource Names],(len([Resource Names])-instr(6,[Resource Names],'_'))),Left([Resource Names],3)='TCW' And instr([Resource Names],',')>0,'Washington' & ' and ' & right([Resource Names],(len([Resource Names])-instr(6,[Resource Names],'_'))),Left([Resource Names],3)='PMP' And instr([Resource Names],',')>0,'Pamplona' & ' and ' & right([Resource Names],(len([Resource Names])-instr(6,[Resource Names],'_'))),Left([Resource Names],4)='TSCD' And instr([Resource Names],',')>0,'Dacice' & ' and ' & right([Resource Names],(len([Resource Names])-instr(6,[Resource Names],'_'))),Left([Resource Names],3)='TCD' And instr([Resource Names],',')>0,'Dusseldorf' & ' and ' & right([Resource Names],(len([Resource Names])-instr(6,[Resource Names],'_'))),Left([Resource Names],3)='TCS' And instr([Resource Names],',')>0,'Shanghai' & ' and ' & right([Resource Names],(len([Resource Names])-instr(6,[Resource Names],'_'))),Left([Resource Names],3)='EXT' And instr([Resource Names],',')>0,'External' & ' and ' & right([Resource Names],(len([Resource Names])-instr(6,[Resource Names],'_'))),instr(6,[Resource Names],'_')=0,mid([Resource Names],instr([Resource Names],'_')+1,(len([Resource Names])-instr([Resource Names],'_')+1)))

    Note: The above formula will work for task with 2 resources properly. But if you have more than 2 resources it will display the output as shown below:
    Shanghai and Shirley,TCW_Washington,PMP_Pamplona

    The 3rd,4th,5th,,,....resources will show up with their Location prefix.

    I hope this works.


    Thanks and Regards, Roopkumar Kamat (MCITP) Please click Mark As Answer; if a post resolves your problem or Vote As Helpful; if a post has been useful to you.

    Tuesday, October 2, 2012 5:09 PM
  • Hi Steve,

    Unfortunately, without a very long and complicated formula such as listed by Roopkumar below.  The short answer is no.  However, how about two text fields - one looking for resources assigned and one looking for location.  Then use a third formula to combine the two fields as needed.

    Julie

    Tuesday, October 2, 2012 9:43 PM
    Moderator
  • Hi Roopkumar,

    Thanks for you effort and time, the fromula you attached does not seem to work but has given me something to work with.

    Thank-you

    Steve

    Wednesday, October 3, 2012 8:31 AM
  • Julie, Roopkumar,

    It seems that the answer was much more simple than we imagined, the following formula works exactly as i wished not matter how many resources are assigned to a task in any order.

    IIf(InStr([Resource Names],'TCUK')>0,'Shirley, ','') & IIf(InStr([Resource Names],'TCS')>0,'Shanghai, ','') & IIf(InStr([Resource Names],'GTC')>0,'Gardone, ','') & IIf(InStr([Resource Names],'TCW')>0,'Washington, ','') & IIf(InStr([Resource Names],'PMP')>0,'Pamplona, ','') & IIf(InStr([Resource Names],'TSCD')>0,'Dačice, ','') & IIf(InStr([Resource Names],'TCD')>0,'Düsseldorf, ','') & IIf(InStr([Resource Names],'EXT')>0,'External, ','')

    Thanks again for your help.

    Regards

    Steve

    • Marked as answer by Steve.D.H Wednesday, October 3, 2012 9:54 AM
    Wednesday, October 3, 2012 9:43 AM
  • Good work Steve.


    Thanks and Regards, Roopkumar Kamat (MCITP) Please click Mark As Answer; if a post resolves your problem or Vote As Helpful; if a post has been useful to you.

    Wednesday, October 3, 2012 9:55 AM