+ Reply to Thread
Results 1 to 7 of 7

Can anyone help simplify this formula

  1. #1
    Registered User
    Join Date
    11-07-2014
    Location
    South Africa
    MS-Off Ver
    Mac 2011
    Posts
    1

    Can anyone help simplify this formula

    HI I have this formula which I need to extend but it is already on the length limit - can someone help me simplify it??

    =IF(+D68='Resource List'!$B$3;'Resource List'!$D$3;IF(+D68='Resource List'!$B$4;'Resource List'!$D$4;IF(+D68='Resource List'!$B$5;'Resource List'!$D$5;IF(+D68='Resource List'!$B$6;'Resource List'!$D$6;IF(+D68='Resource List'!$B$7;'Resource List'!$D$7;IF(+D68='Resource List'!$B$8;'Resource List'!$D$8;IF(+D68='Resource List'!$B$9;'Resource List'!$D$9;IF(+D68='Resource List'!$B$10;'Resource List'!$D$10;IF(+D68='Resource List'!$B$11;'Resource List'!$D$11;IF(+D68='Resource List'!$B$12;'Resource List'!$D$12;IF(+D68='Resource List'!$B$13;'Resource List'!$D$13;IF(+D68='Resource List'!$B$14;'Resource List'!$D$14;IF(+D68='Resource List'!$B$15;'Resource List'!$D$15;IF(+D68='Resource List'!$B$16;'Resource List'!$D$16;IF(+D68='Resource List'!$B$17;'Resource List'!$D$17;IF(+D68='Resource List'!$B$18;'Resource List'!$D$18;IF(+D68='Resource List'!$B$19;'Resource List'!$D$19;IF(+D68='Resource List'!$B$20;'Resource List'!$D$20;IF(+D68='Resource List'!$B$21;'Resource List'!$D$21;IF(+D68='Resource List'!$B$22;'Resource List'!$D$22;IF(+D68='Resource List'!$B$23;'Resource List'!$D$23;IF(+D68='Resource List'!$B$24;'Resource List'!$D$24;IF(+D68='Resource List'!$B$25;'Resource List'!$D$25;IF(+D68='Resource List'!$B$26;'Resource List'!$D$26;IF(+D68='Resource List'!$B$27;'Resource List'!$D$27;IF(+D68='Resource List'!$B$28;'Resource List'!$D$28;IF(+D68='Resource List'!$B$29;'Resource List'!$D$29;IF(+D68='Resource List'!$B$30;'Resource List'!$D$30;IF(+D68='Resource List'!$B$31;'Resource List'!$D$31;IF(+D68='Resource List'!$B$32;'Resource List'!$D$32;IF(+D68='Resource List'!$B$33;'Resource List'!$D$33;IF(+D68='Resource List'!$B$34;'Resource List'!$D$34;IF(+D68='Resource List'!$B$35;'Resource List'!$D$35;IF(+D68='Resource List'!$B$36;'Resource List'!$D$36;IF(+D68='Resource List'!$B$37;'Resource List'!$D$37;IF(+D68='Resource List'!$B$38;'Resource List'!$D$38;IF(+D68='Resource List'!$B$39;'Resource List'!$D$39;IF(+D68='Resource List'!$B$40;'Resource List'!$D$40;IF(+D68='Resource List'!$B$41;'Resource List'!$D$41;IF(+D68='Resource List'!$B$42;'Resource List'!$D$42;IF(+D68='Resource List'!$B$43;'Resource List'!$D$43;IF(+D68='Resource List'!$B$44;'Resource List'!$D$44;IF(+D68='Resource List'!$B$45;'Resource List'!$D$45;IF(+D68='Resource List'!$B$46;'Resource List'!$D$46;IF(+D68='Resource List'!$B$47;'Resource List'!$D$47;IF(+D68='Resource List'!$B$48;'Resource List'!$D$48;IF(+D68='Resource List'!$B$49;'Resource List'!$D$49;IF(+D68='Resource List'!$B$50;'Resource List'!$D$50;IF(+D68='Resource List'!$B$51;'Resource List'!$D$51;IF(+D68='Resource List'!$B$52;'Resource List'!$D$52;IF(+D68='Resource List'!$B$53;'Resource List'!$D$53;IF(+D68='Resource List'!$B$54;'Resource List'!$D$54;IF(+D68='Resource List'!$B$55;'Resource List'!$D$55;IF(+D68='Resource List'!$B$56;'Resource List'!$D$56;IF(+D68='Resource List'!$B$57;'Resource List'!$D$57;IF(+D68='Resource List'!$B$58;'Resource List'!$D$58;IF(+D68='Resource List'!$B$59;'Resource List'!$D$59;IF(+D68='Resource List'!$B$60;'Resource List'!$D$60;IF(+D68='Resource List'!$B$61;'Resource List'!$D$61;IF(+D68='Resource List'!$B$62;'Resource List'!$D$62;IF(+D68='Resource List'!$B$63;'Resource List'!$D$63;IF(+D68='Resource List'!$B$64;'Resource List'!$D$64;IF(+D68='Resource List'!$B$65;'Resource List'!$D$65;IF(+D68='Resource List'!$B$66;'Resource List'!$D$66;IF(+D68='Resource List'!$B$67;'Resource List'!$D$67;"ERROR")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

  2. #2
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Can anyone help simplify this formula

    Use Index or vlookup

    Better provide a sample workbook without any confidential data

    Punnam

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Can anyone help simplify this formula

    Hi, and welcome to the forum.

    Must be about the biggest I've seen!

    Almost certainly you should use a VLOOKUP function.

    For more specific help you'll need to upload the workbook.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    11-25-2013
    Location
    Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: Can anyone help simplify this formula

    You should be able to replace this entire formula with a VLOOKUP.

    =VLOOKUP(D68,$B$3:$D$67,3,FALSE)

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Can anyone help simplify this formula

    ...an addendum

    Try

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-25-2013
    Location
    Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: Can anyone help simplify this formula

    Good point, Richard! I missed that

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Can anyone help simplify this formula

    @Laurena

    I had posted #5 before I'd seen your #4.

    However I believe your formula is correct with the '3' offset and mine with the '2' is wrong.
    A good example of how much easier these things are if the OP attaches the original workbook rather than text (or pictures).

    I presume your #6 was a reference to the list separator being a ; rather than ,

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. simplify formula
    By midwest trader in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-28-2014, 12:53 PM
  2. Help simplify my formula!
    By missxmelon in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-23-2013, 04:24 PM
  3. Cannot simplify =sum formula
    By amberpyxie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-29-2012, 10:47 AM
  4. simplify this formula
    By Dave F in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-07-2006, 05:40 PM
  5. Simplify formula
    By Luke in forum Excel Formulas & Functions
    Replies: 38
    Last Post: 05-06-2005, 03:06 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1