Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Driving me nuts. Need more nested than 7
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Stressed
 
Posts: n/a
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default
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


Reply With QuoteReply With Quote
  #3   Report Post  
Stressed
 
Posts: n/a
Default
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



Reply With QuoteReply With Quote
  #4   Report Post  
Stressed
 
Posts: n/a
Default
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



Reply With QuoteReply With Quote
  #5   Report Post  
Fredrik Wahlgren
 
Posts: n/a
Default

"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


Reply With QuoteReply With Quote
  #6   Report Post  
Bernie Deitrick
 
Posts: n/a
Default
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





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help: Scroll wheel toggles between up and down, and left and right - is driving me crazy Dab Excel Discussion (Misc queries) 3 March 14th 05 03:17 PM
Nested IF statements John Simons Excel Worksheet Functions 14 February 16th 05 06:17 AM
€śUse of more then 7 nested if statement€ť Faisal Yameen Excel Worksheet Functions 3 January 12th 05 06:02 PM
7+ nested if statement? Turi Excel Worksheet Functions 3 December 20th 04 07:55 PM
Excel / VB is driving me nuts!! Andrew Excel Worksheet Functions 2 November 29th 04 04:06 AM


All times are GMT +1. The time now is 01:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement