+ Reply to Thread
Results 1 to 7 of 7

IF function help needed please

Hybrid View

ballst IF function help needed please 03-14-2014, 12:53 PM
TMS Re: IF function help needed... 03-14-2014, 01:02 PM
ballst Re: IF function help needed... 03-14-2014, 01:46 PM
TMS Re: IF function help needed... 03-14-2014, 02:40 PM
ballst Re: IF function help needed... 03-27-2014, 12:54 PM
ballst Re: IF function help needed... 04-03-2014, 10:35 AM
arlu1201 Re: IF function help needed... 04-03-2014, 10:58 AM
  1. 03-14-2014, 12:53 PM #1
    ballst is offline
    Registered User
    Join Date
    12-06-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    21

    IF function help needed please

    Hi all,

    First up I'm using Word 2003 as I've already learnt this can make a difference!

    I'm looking for some kind of If function which can look at part of a URN and dependent on the cell content displays a certain value.

    The URN will be copied into a cell and will look like this "ABC/0012/OLR/2014/0001"

    The part which I need to concentrate on is the OLR part, which is the identifying part of the reference.

    So far when testing I'm using the following two formula, but neither are completely suitbale for my needs.

    =IF(ISERROR(FIND("OAF",B6)),"Leasehold 93","Leasehold 67") Problem with this is that it is one or the other and I've got 80+ to incorporate

    =IF(ISNUMBER(SEARCH("OAF",B6)),"Section 21",IF(ISNUMBER(SEARCH("OLR",B6)),"Section 48",IF(ISNUMBER(SEARCH("OCE",B6)),"Section 24",IF(ISNUMBER(SEARCH("OC",B6)),"Section 60","")))) The second go would just seem too long to construct.

    As I've got 80+ differing codes and outcomes I'm guessing some kind of range or list is probably the way to go, but I don't have a clue how to put it together, hence my post.

    An idea solution should be able to read the "OLR" bit from one cell and then display the outcome in another cell. There are then variations on this theme but I can then hopefully work that out myself if somebody could point me in the right direction!

    Grateful for any assistance you can spare the time to provide! Advanced apologies but I'm next to useless with macro's etc!

    Cheers,

    Ballst
    Reply With QuoteRegister To Reply
  2. 03-14-2014, 01:02 PM #2
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,155

    Re: IF function help needed please

    Does the URN always have the same number of characters in each of the sections (between the slashes)?

    If so, you could set up a list and use MID to extract the code and use that in a VLOOKUP.

    Or, if the codes have unique numbers between 1 and 80, you could get away with MATCH

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 03-14-2014, 01:46 PM #3
    ballst is offline
    Registered User
    Join Date
    12-06-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: IF function help needed please

    Quote Originally Posted by TMS View Post
    Does the URN always have the same number of characters in each of the sections (between the slashes)?

    If so, you could set up a list and use MID to extract the code and use that in a VLOOKUP.

    Or, if the codes have unique numbers between 1 and 80, you could get away with MATCH

    Regards, TMS
    Hi TMS,

    Yep the URN is always the same format ie xxx/xxxx/xxx/xxxx/xxx

    Excuse my ignorance but not familiar with MID, though I have heard of VLOOKUP.

    The codes are not numerical, always three alpha characters.

    Cheers, Ballst
    Reply With QuoteRegister To Reply
  4. 03-14-2014, 02:40 PM #4
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,155

    Re: IF function help needed please

    See the example workbook attached.

    Formula: copy to clipboard 
    ="Section " & VLOOKUP(MID($A2,10,3),Reference!$A:$B,2,FALSE)

    Formula: copy to clipboard 
    ="Section " & MATCH(MID($A2,10,3),Reference!$D:$D,0)

    Formula: copy to clipboard 
    ="Section " & VLOOKUP(MID($A2,10,3),CodeTable,2,FALSE)



    Regards, TMS
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 03-27-2014, 12:54 PM #5
    ballst is offline
    Registered User
    Join Date
    12-06-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: IF function help needed please

    Hi TMS,

    Just a quick note of thanks for your response. I've not been in a position to log back into until today, so was unaware of such a detailed response until now.

    I'll have a look at it now and report back later today or tomorrow, but for now thanks!

    Ballst
    Reply With QuoteRegister To Reply
  6. 04-03-2014, 10:35 AM #6
    ballst is offline
    Registered User
    Join Date
    12-06-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: IF function help needed please

    TMS,Happy to report this worked like a charm.

    Thanks again!
    Reply With QuoteRegister To Reply
  7. 04-03-2014, 10:58 AM #7
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: IF function help needed please

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. If Then Function Help needed
    By wheatgrass in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-14-2012, 01:41 PM
  2. Help Needed with IF Function
    By Zyphon in forum Excel General
    Replies: 1
    Last Post: 04-02-2007, 06:32 PM
  3. Help needed with function
    By Kevin in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-28-2006, 02:35 AM
  4. Help needed with sum function
    By Angela@Singh.com in forum Excel General
    Replies: 7
    Last Post: 08-24-2005, 08:05 PM
  5. Help needed with IF function
    By Ed in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-23-2005, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1