+ Reply to Thread
Results 1 to 4 of 4

overcoming nested IF limitations...with VBA?

  1. #1
    Registered User
    Join Date
    10-11-2005
    Posts
    13

    Question overcoming nested IF limitations...with VBA?

    Can anyone tell me how to sneak past the function-nesting limitations?
    Eventually, I'll be doing a good deal of this stuff with the help of Access, but my company has yet to provide that program.

    I don't know how to use the INDEX function, which looks like it might work if all references were one worksheet. Can it also be applied if you begin references globally (i.e., from the workbook)?

    I'm learning VBA and suspect there may be a way to use it to do this, but haven't the needed skills at this time.

    Here's the formula I want to use (with the option of adding more nests for more addresses):

    =IF('54th'!C133="",IF('60th'!C127,IF(Balboa!C133,IF(Canyon!C135,IF(Collwood!C128,IF(Florida!C123,IF(Graves!C122,IF('4641 Ohio'!C122,'MPI Labor (S)'!(Orange!C133,IF('605 S. Mollison'!C137,IF('San Ramon'!C122,IF(Wilson!C122,"Wilson","")"San Ramon"),"605 S. Mollison"),"Orange"),"4641 Ohio"),"Graves"),"Florida"),"Collwood"),"Canyon"),"Balboa"),"60th"),"54th")

    Can anyone help?

    Thanks in advance,
    jc

  2. #2
    Harlan Grove
    Guest

    Re: overcoming nested IF limitations...with VBA?

    Simplifying your formula (in part to avoid dealing with some typos
    which would render it syntactically invalid no matter how many function
    nesting levels Excel provided) to

    =IF(A="",IF(B="",IF(C="",IF(D="",IF(E="",IF(F="",IF(G="",IF(H="",IF(I="",IF(J="",j,""),
    i),h),g),f),e),d),c),b),a)

    you could rewrite it as

    =LOOKUP(2,1/({1;0;0;0;0;0;0;0;0;0;0}+(J="")*{0;1;0;0;0;0;0;0;0;0;0}
    +(I="")*{0;0;1;0;0;0;0;0;0;0;0}+(H="")*{0;0;0;1;0;0;0;0;0;0;0}+(G="")*{0;0;0;0;1;0;0;0;0;0;0}
    +(F="")*{0;0;0;0;0;1;0;0;0;0;0}+(E="")*{0;0;0;0;0;0;1;0;0;0;0}+(D="")*{0;0;0;0;0;0;0;1;0;0;0}
    +(C="")*{0;0;0;0;0;0;0;0;1;0;0}+(B="")*{0;0;0;0;0;0;0;0;0;1;0}+(A="")*{0;0;0;0;0;0;0;0;0;0;1}),
    {"",j,i,h,g,f,e,d,c,b,a})

    If you used the same cell address in each worksheet, simpler formulas
    would be possible.


  3. #3
    Registered User
    Join Date
    10-11-2005
    Posts
    13
    Thanks for your help.

    What simpler formulas would be possible with the same cell reference on each sheet?

    jlc

  4. #4
    Harlan Grove
    Guest

    Re: overcoming nested IF limitations...with VBA?

    JLC wrote...
    ....
    >What simpler formulas would be possible with the same cell reference on
    >each sheet?


    Your original formula looked malformed. I'm assuming you want the value
    of the first nonblank cell from a group of individual cells in
    different worksheets. If so, you could put a list of the worksheet
    names IN THE ORDER YOU WANT THEM SEARCHED in a range on another
    worksheet and name it something like WSLST. If each of these worksheets
    used cell B5 for the information you're checking, then you could find
    the first nonblank value using an array formula like

    =VLOOKUP("?*",T(INDIRECT("'"&WSLST&"'!B5")),1,0)

    and if you wanted to change the order in which to search these
    worksheets, all you'd need to do is change the order of the worksheet
    names in WSLST, and the formulas could be left as-is.


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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