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!M1 56,(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),Te xas!Q156,(IF(AND(C28="Metro",C30=60),Texas!R156,(I F(AND(C28="CompleteLink",C30=12),Texas!S156,(IF(AN D(C28="CompleteLink",C30=24),Texas!T156,(IF(AND(C2 8="CompleteLink",C30=36),Texas!U156,(IF(AND(C28="C ompleteLink",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" wrote in message
...
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="Si mpleLink",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!K15 6,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!Q1 56,(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="Compl eteLink",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" wrote in message
...
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="Si mpleLink",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!K15 6,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!Q1 56,(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="Compl eteLink",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" wrote in message
...
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="Si mpleLink",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!K15 6,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!Q1 56,(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="Compl eteLink",C30=36),Texas!U15
6,(IF(AND(C28="CompleteLink",C30=60),Texas!V156,0) ))))))))))))))))))))))

I know its hefty sorry

"Stressed" wrote in message
...
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="Si mpleLink",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!K15 6,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!Q1 56,(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="Compl eteLink",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

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" wrote in message
...
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" wrote in message
...
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="Si mpleLink",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!K15 6,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!Q1 56,(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="Compl eteLink",C30=36),Texas!U15
6,(IF(AND(C28="CompleteLink",C30=60),Texas!V156,0) ))))))))))))))))))))))

I know its hefty sorry