Driving me nuts. Need more nested than 7

This is the formula im trying to accomplish but xl wont let me nest more than
seven formulas in one.... any suggestions on what i can do????

=IF(AND(C28="CBS",C30=12),Texas!E156,(IF(AND(C28="CBS",C30=24),Texas!F156,(IF(AND(C28="CBS",C30=36),Texas!G156,(IF(AND(C28="SimpleLink",C30=12),Texas!H156,(IF(AND(C28="SimpleLink",C30=24),Texas!I156,(IF(AND(C28="SimpleLink",C30=36),Texas!J156,(IF(AND(C28="TVD",C30=12),Texas!K156,IF(AND(C28="TVD",C30=24),Texas!L156,(IF(AND(C28="TVD",C30=36),Texas!M156,(IF(AND(C28="Metro",C30=12),Texas!N156,(IF(AND(C28="Metro",C30=24),Texas!O156,(IF(AND(C28="Metro",C30=36),Texas!P156,(IF(AND(C28="Metro",C30=48),Texas!Q156,(IF(AND(C28="Metro",C30=60),Texas!R156,(IF(AND(C28="CompleteLink",C30=12),Texas!S156,(IF(AND(C28="CompleteLink",C30=24),Texas!T156,(IF(AND(C28="CompleteLink",C30=36),Texas!U156,(IF(AND(C28="CompleteLink",C30=60),Texas!V156,0)))))))))))))))))))))))

I know its hefty sorry

Stressed,

The easiest way around this is to put labels onto sheet Texas, cells
E155:V155, where the labels are combinations of whar you are looking for
(within the AND functions) like
CBS12,CBS24,.....CompleteLink60

And then use HLOOKUP

=HLOOKUP(C28&C30,Texas!E155:V156,2,False)

HTH,
Bernie
MS Excel MVP


"Stressed" <[email protected]> wrote in message
news:[email protected]...
> This is the formula im trying to accomplish but xl wont let me nest more
than
> seven formulas in one.... any suggestions on what i can do????
>
>
=IF(AND(C28="CBS",C30=12),Texas!E156,(IF(AND(C28="CBS",C30=24),Texas!F156,(I
F(AND(C28="CBS",C30=36),Texas!G156,(IF(AND(C28="SimpleLink",C30=12),Texas!H1
56,(IF(AND(C28="SimpleLink",C30=24),Texas!I156,(IF(AND(C28="SimpleLink",C30=
36),Texas!J156,(IF(AND(C28="TVD",C30=12),Texas!K156,IF(AND(C28="TVD",C30=24)
,Texas!L156,(IF(AND(C28="TVD",C30=36),Texas!M156,(IF(AND(C28="Metro",C30=12)
,Texas!N156,(IF(AND(C28="Metro",C30=24),Texas!O156,(IF(AND(C28="Metro",C30=3
6),Texas!P156,(IF(AND(C28="Metro",C30=48),Texas!Q156,(IF(AND(C28="Metro",C30
=60),Texas!R156,(IF(AND(C28="CompleteLink",C30=12),Texas!S156,(IF(AND(C28="C
ompleteLink",C30=24),Texas!T156,(IF(AND(C28="CompleteLink",C30=36),Texas!U15
6,(IF(AND(C28="CompleteLink",C30=60),Texas!V156,0)))))))))))))))))))))))
>
> I know its hefty sorry

i did it exactly how you said but it didnt work i get #n/a in the cell....
Sorry this is the 1st time i have used hlookup for anything, not sure exactly
how it works... Thanks for your help though



"Bernie Deitrick" wrote:

> Stressed,
>
> The easiest way around this is to put labels onto sheet Texas, cells
> E155:V155, where the labels are combinations of whar you are looking for
> (within the AND functions) like
> CBS12,CBS24,.....CompleteLink60
>
> And then use HLOOKUP
>
> =HLOOKUP(C28&C30,Texas!E155:V156,2,False)
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Stressed" <[email protected]> wrote in message
> news:[email protected]...
> > This is the formula im trying to accomplish but xl wont let me nest more
> than
> > seven formulas in one.... any suggestions on what i can do????
> >
> >
> =IF(AND(C28="CBS",C30=12),Texas!E156,(IF(AND(C28="CBS",C30=24),Texas!F156,(I
> F(AND(C28="CBS",C30=36),Texas!G156,(IF(AND(C28="SimpleLink",C30=12),Texas!H1
> 56,(IF(AND(C28="SimpleLink",C30=24),Texas!I156,(IF(AND(C28="SimpleLink",C30=
> 36),Texas!J156,(IF(AND(C28="TVD",C30=12),Texas!K156,IF(AND(C28="TVD",C30=24)
> ,Texas!L156,(IF(AND(C28="TVD",C30=36),Texas!M156,(IF(AND(C28="Metro",C30=12)
> ,Texas!N156,(IF(AND(C28="Metro",C30=24),Texas!O156,(IF(AND(C28="Metro",C30=3
> 6),Texas!P156,(IF(AND(C28="Metro",C30=48),Texas!Q156,(IF(AND(C28="Metro",C30
> =60),Texas!R156,(IF(AND(C28="CompleteLink",C30=12),Texas!S156,(IF(AND(C28="C
> ompleteLink",C30=24),Texas!T156,(IF(AND(C28="CompleteLink",C30=36),Texas!U15
> 6,(IF(AND(C28="CompleteLink",C30=60),Texas!V156,0)))))))))))))))))))))))
> >
> > I know its hefty sorry
>
>
>

I did it exactly how you told me and i get a #n/a.... Im new to the hlookup
function so it may be errror on my part but i cant get it to work..... Thanks
for your help, anything else i could do to fix it?

"Bernie Deitrick" wrote:

> Stressed,
>
> The easiest way around this is to put labels onto sheet Texas, cells
> E155:V155, where the labels are combinations of whar you are looking for
> (within the AND functions) like
> CBS12,CBS24,.....CompleteLink60
>
> And then use HLOOKUP
>
> =HLOOKUP(C28&C30,Texas!E155:V156,2,False)
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Stressed" <[email protected]> wrote in message
> news:[email protected]...
> > This is the formula im trying to accomplish but xl wont let me nest more
> than
> > seven formulas in one.... any suggestions on what i can do????
> >
> >
> =IF(AND(C28="CBS",C30=12),Texas!E156,(IF(AND(C28="CBS",C30=24),Texas!F156,(I
> F(AND(C28="CBS",C30=36),Texas!G156,(IF(AND(C28="SimpleLink",C30=12),Texas!H1
> 56,(IF(AND(C28="SimpleLink",C30=24),Texas!I156,(IF(AND(C28="SimpleLink",C30=
> 36),Texas!J156,(IF(AND(C28="TVD",C30=12),Texas!K156,IF(AND(C28="TVD",C30=24)
> ,Texas!L156,(IF(AND(C28="TVD",C30=36),Texas!M156,(IF(AND(C28="Metro",C30=12)
> ,Texas!N156,(IF(AND(C28="Metro",C30=24),Texas!O156,(IF(AND(C28="Metro",C30=3
> 6),Texas!P156,(IF(AND(C28="Metro",C30=48),Texas!Q156,(IF(AND(C28="Metro",C30
> =60),Texas!R156,(IF(AND(C28="CompleteLink",C30=12),Texas!S156,(IF(AND(C28="C
> ompleteLink",C30=24),Texas!T156,(IF(AND(C28="CompleteLink",C30=36),Texas!U15
> 6,(IF(AND(C28="CompleteLink",C30=60),Texas!V156,0)))))))))))))))))))))))
> >
> > I know its hefty sorry
>
>
>

Stressed,

I will send you a working example if you contact me privately. Take the
spaces out of my eamil address and change dot to .

HTH,
Bernie
MS Excel MVP


"Stressed" <[email protected]> wrote in message
news:[email protected]...
> i did it exactly how you said but it didnt work i get #n/a in the cell....
> Sorry this is the 1st time i have used hlookup for anything, not sure
exactly
> how it works... Thanks for your help though
>
>
>
> "Bernie Deitrick" wrote:
>
> > Stressed,
> >
> > The easiest way around this is to put labels onto sheet Texas, cells
> > E155:V155, where the labels are combinations of whar you are looking for
> > (within the AND functions) like
> > CBS12,CBS24,.....CompleteLink60
> >
> > And then use HLOOKUP
> >
> > =HLOOKUP(C28&C30,Texas!E155:V156,2,False)
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Stressed" <[email protected]> wrote in message
> > news:[email protected]...
> > > This is the formula im trying to accomplish but xl wont let me nest
more
> > than
> > > seven formulas in one.... any suggestions on what i can do????
> > >
> > >
> >
=IF(AND(C28="CBS",C30=12),Texas!E156,(IF(AND(C28="CBS",C30=24),Texas!F156,(I
> >
F(AND(C28="CBS",C30=36),Texas!G156,(IF(AND(C28="SimpleLink",C30=12),Texas!H1
> >
56,(IF(AND(C28="SimpleLink",C30=24),Texas!I156,(IF(AND(C28="SimpleLink",C30=
> >
36),Texas!J156,(IF(AND(C28="TVD",C30=12),Texas!K156,IF(AND(C28="TVD",C30=24)
> >
,Texas!L156,(IF(AND(C28="TVD",C30=36),Texas!M156,(IF(AND(C28="Metro",C30=12)
> >
,Texas!N156,(IF(AND(C28="Metro",C30=24),Texas!O156,(IF(AND(C28="Metro",C30=3
> >
6),Texas!P156,(IF(AND(C28="Metro",C30=48),Texas!Q156,(IF(AND(C28="Metro",C30
> >
=60),Texas!R156,(IF(AND(C28="CompleteLink",C30=12),Texas!S156,(IF(AND(C28="C
> >
ompleteLink",C30=24),Texas!T156,(IF(AND(C28="CompleteLink",C30=36),Texas!U15
> > 6,(IF(AND(C28="CompleteLink",C30=60),Texas!V156,0)))))))))))))))))))))))
> > >
> > > I know its hefty sorry
> >
> >
> >

"Stressed" <[email protected]> wrote in message
news:[email protected]...
> This is the formula im trying to accomplish but xl wont let me nest more
than
> seven formulas in one.... any suggestions on what i can do????
>
>
=IF(AND(C28="CBS",C30=12),Texas!E156,(IF(AND(C28="CBS",C30=24),Texas!F156,(I
F(AND(C28="CBS",C30=36),Texas!G156,(IF(AND(C28="SimpleLink",C30=12),Texas!H1
56,(IF(AND(C28="SimpleLink",C30=24),Texas!I156,(IF(AND(C28="SimpleLink",C30=
36),Texas!J156,(IF(AND(C28="TVD",C30=12),Texas!K156,IF(AND(C28="TVD",C30=24)
,Texas!L156,(IF(AND(C28="TVD",C30=36),Texas!M156,(IF(AND(C28="Metro",C30=12)
,Texas!N156,(IF(AND(C28="Metro",C30=24),Texas!O156,(IF(AND(C28="Metro",C30=3
6),Texas!P156,(IF(AND(C28="Metro",C30=48),Texas!Q156,(IF(AND(C28="Metro",C30
=60),Texas!R156,(IF(AND(C28="CompleteLink",C30=12),Texas!S156,(IF(AND(C28="C
ompleteLink",C30=24),Texas!T156,(IF(AND(C28="CompleteLink",C30=36),Texas!U15
6,(IF(AND(C28="CompleteLink",C30=60),Texas!V156,0)))))))))))))))))))))))
>
> I know its hefty sorry

This is a mega formula. In how many cells do you want to use it? If you want
to use it many times, I think a User defined Fuction would be the best
solution. You would need to come up with good names for all cell refernces.

/Fredrik