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,I F(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


--
JLC
------------------------------------------------------------------------
JLC's Profile: http://www.excelforum.com/member.php...o&userid=28014
View this thread: http://www.excelforum.com/showthread...hreadid=482062

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="",I F(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.

overcoming nested IF limitations...with VBA?
 

Thanks for your help.

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

jlc


--
JLC
------------------------------------------------------------------------
JLC's Profile: http://www.excelforum.com/member.php...o&userid=28014
View this thread: http://www.excelforum.com/showthread...hreadid=482062

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.