#### 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????

I know its hefty sorry
```
 0
Stressed (9)
4/12/2005 4:44:01 PM
excel.misc 78881 articles. 5 followers.

5 Replies
284 Views

Similar Articles

[PageSpeed] 49

```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

And then use HLOOKUP

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

HTH,
Bernie
MS Excel MVP

"Stressed" <Stressed@discussions.microsoft.com> wrote in message
news:EC0BD400-6CD4-4742-BA95-B9413CB4B418@microsoft.com...
> 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
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
>
> I know its hefty sorry

```
 0
Bernie
4/12/2005 4:57:58 PM
```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
>
> And then use HLOOKUP
>
> =HLOOKUP(C28&C30,Texas!E155:V156,2,False)
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Stressed" <Stressed@discussions.microsoft.com> wrote in message
> news:EC0BD400-6CD4-4742-BA95-B9413CB4B418@microsoft.com...
> > 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
> 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
> >
> > I know its hefty sorry
>
>
>
```
 0
Stressed (9)
4/12/2005 5:15:02 PM
```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
>
> And then use HLOOKUP
>
> =HLOOKUP(C28&C30,Texas!E155:V156,2,False)
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Stressed" <Stressed@discussions.microsoft.com> wrote in message
> news:EC0BD400-6CD4-4742-BA95-B9413CB4B418@microsoft.com...
> > 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
> 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
> >
> > I know its hefty sorry
>
>
>
```
 0
Stressed (9)
4/12/2005 5:18:04 PM
```"Stressed" <Stressed@discussions.microsoft.com> wrote in message
news:EC0BD400-6CD4-4742-BA95-B9413CB4B418@microsoft.com...
> 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
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
>
> 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

```
 0
4/12/2005 5:19:40 PM
```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" <Stressed@discussions.microsoft.com> wrote in message
news:A143116E-3CC6-41DC-AE5A-C07D6C73882C@microsoft.com...
> 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
> >
> > And then use HLOOKUP
> >
> > =HLOOKUP(C28&C30,Texas!E155:V156,2,False)
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Stressed" <Stressed@discussions.microsoft.com> wrote in message
> > news:EC0BD400-6CD4-4742-BA95-B9413CB4B418@microsoft.com...
> > > 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
> >
> >
> >
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
> >
> >
> > >
> > > I know its hefty sorry
> >
> >
> >

```
 0
Bernie
4/12/2005 5:20:57 PM

Similar Artilces:

Need WritePrivateProfileString replacement
I've replaced my registry entries using an ini file, but I've just discovered that I can only have 254 bytes of text stored in a key, or half that for unicode, so without having to use another file structure, I want to find another function that supports greater lengths. "Jimekus" <jimekus@gmail.com> wrote in message news:a8f69a99-1d9a-40e8-af92-c0cd34cf7fbf@h14g2000pri.googlegroups.com... > I've replaced my registry entries using an ini file, but I've just > discovered that I can only have 254 bytes of text stored in a key, or > half that f...

Starting off band needs help!
Hi Everyone, We are a new band just getting it together. We need help! We are naming our band "THE SCENE" and would love for anyone who could come up with a logo (or any type of art) to please send it to us. We have no money... We would just be so thankful if you could send something, anything! If you come up with any ideas (logo, art or anything) for out band THE SCENE please email them to us at: thetampabassman@live.com THANKS SO MUCH FOR YOUR HELP!!! ps: I guess jpg would be the best. Right? ...

Decent AR Report Needed
I cannot believe RMS doesn't ship with an AR report that's worth looking at. I want ALL AR ACTIVITY for any given customer on one report. Transactions, Payments, Late Fees/Interest Charges, Global Adjustments, everything. I don't want a report for receivables and a separate report for payments like they have in the Reports Library. If you have an AR report that's worth the paper it takes to print it, I'd love to see it. Thanks, Tom -- Stop fishing for e-mail ...

need help :)
http://kezzysworld.bravehost.com visit my site if u wanna chat or have fun :) Thanks ;) ...

Disable Drives....?
How to disable drives(including USB) through MFC. Once the drive is disabled there shouldn't be any access to the drive. Suggestions are appreciated. On Feb 15, 9:57 am, "Raj Gopal" <indupuri...@gmail.com> wrote: > How to disable drives(including USB) through MFC. > Once the drive is disabled there shouldn't be any access to the drive. > Suggestions are appreciated. There is nothing in MFC which does this. You will need to go to a lower level to do this, if at all this is possible to do so. --- Ajay check this out .......... http://support.microsoft.com/...

USB drive shows a removable drive
USB drive shows a removable drive. the removable drive take up several MB of space. How can i delete this off the drive. I have a Vista Bussines laptop Bet bet wrote: > USB drive shows a removable drive. the removable drive take up > several MB of space. > How can i delete this off the drive. I have a Vista Bussines laptop > > Bet ================================ Vista Business? OK...you are posting in a Windows XP newsgroup. Anyway..... Go to...Start / Computer... Right click the icon for the removable disk... Choose.....Explore... This shoul...

Need to supress display of FALSE in a cell
I have entered a formula that seems to work ok, but is displaying false if an invalid code is entered into one of the input cells. Is there any way to suppress the display of FALSE and just display spaces? The formula in question is as follows: =IF(I17="LH",(SUM(I28:M29)*30),IF(I17="OH1",(SUM(I28:M29)*90),IF(I17="OH2",(SUM(I28:M29)*90),IF(I17="EH1",(SUM(I28:M29)*120),IF(I17="EH2",(SUM(I28:M29)*120)))))) Thanks for the help!! Ken K. - 2191 -- akkrug Give the formula a FALSE argument: =IF(I17="LH",SUM(I28:M29)*30,IF(I17=&quo...

Granny needs help!
I want to create a "goto" on exit so that when I exit a cell, in certain row, the cursor jumps several columns over (not the nex column) to a column I designate. I know some VB but cannot find how o where to enter that command or script. Like, when I am dome entering data in column "D" instead of having t tab over to column "M", it would automatically jump right to colum "M". MUCHO THANKS!! -- Message posted from http://www.ExcelForum.com Take a look at http://www.xldynamic.com/source/xld.xlFAQ0008.html -- HTH Bob Phillips ... looking ou...

Tissot Men's T-Touch Titanium Watch #T33.7.638.81
Price:\$850.00 Image: http://bestdeallocator.info/image.php?id=B000FBXRRW Best deal: http://bestdeallocator.info/index.php?id=B000FBXRRW Titanium case. Blue leather strap. Blue mother-of-pearl dial. Bidirectional rotating compass bezel. Tactile scratch resistant sapphire crystal. Push button deployment clasp. Multifunction touchscreen. Analog and digital display. Thermometer. Barometer. Altimeter. Alarm. Case diameter 42mm. Case thickness 13mm. Quartz movement. Water resistant at 30 meters (100 feet). Alternate model number T33763881. Tissot T-Touch Titanium Blue Mother-of-pearl Analog/D...

Need to create a mailbox that cannot be replied to
Need to create a mailbox that cannot be replied to: It is a forwarding account, so if people reply to it, their reply will be forwarded back to the entire company. you can restrict who can send email to it in "Delivery Restrictions"... -- Susan Conkey [MVP] "daexchange" <daexchange@discussions.microsoft.com> wrote in message news:B09ECACD-FFA0-42DE-B47F-7D2E1E3D1261@microsoft.com... > Need to create a mailbox that cannot be replied to: > > It is a forwarding account, so if people reply to it, their reply will be > forwarded back to the entire comp...

Need to get from Excel to Word ...somehow!
I put out a weekly newsletter that compiles information from varioius websites, a portion of this is developed from Excel. ...I start sorting as many as 30,000 articles and winnow it down to 85. I wind up with one column that I need to transfer to HTML and have it where I can edit it. This was no problem up until this weekend when after a hard drive crashed I upgraded from Office 97 to XP Office, and I had to upgrade from Mozilla to SeaMonkey for HTML editing. Within the Excel Sort column I would begin with: News Story Title (with hyperlink) Synopsis of news story News Story Titl...

control of external drive?
(XP Pro) I have Process Explorer, which tells me a lot about what is happening on the C: drive, but I find that external USB drives (MyBook 500gigs) are tending to hang, and I cannot see how to find out what programme is causing it. Any ideas how to tell what external drives are doing? Cheers, S Could it be your AV Software that is Scanning it upon detection? Russ -- Russell Grover - SBITS.Biz [SBS-MVP] Microsoft Gold Certified Partner Microsoft Certified Small Business Specialist 24hr SBS Remote Support - http://www.SBITS.Biz Second IT Opinion http://www.persona...

Excluding full path to linked file on a networked drive
Hello, I have two excel files -- A and B. Both files are saved on a network drive in the same directory. In file A, I have links to cells in file B. Because the files are in the same directory, is it possible to not have Excel show the entire path to file B in the formulas in file A that link to file B? The network path is quite long and it makes it difficult to read the formulas? If file B is open, the formulas just show the name of the file,i.e. [B]. Is it possible to always have this shown? THanks, How about using a cell reference that contains the file path? -- Jim Cone Portlan...

SQL 2008 linked server to VFP 7.x tables??
Does a good reference or tutorial exist for this topic?? We only need READ access from SQL 2008 to the VFP tables -- various select queries for reporting etc. Should this be done by first installing the most current VFPOLE driver onto the server containing SQL 2008, then establishing the linked server?? Or should we use an ODBC DSN (file or system, which??) and link from SQL 2008 to the VFP data this way?? Thank you, Tom ...

Increase Drop shadow in word 7
Is there a way to increase the size of a drop shadow in Word 7 without using WordArt? On Sat, 19 Dec 2009 19:34:01 -0800, Mamaduke <Mamaduke@discussions.microsoft.com> wrote: >Is there a way to increase the size of a drop shadow in Word 7 without using >WordArt? No, not in Word 2007 (which, BTW, is not "Word 7"). But Word 2010, which is currently available for free download as a public beta, does have that feature for regular text. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post a...

XmlReader, asp.net 2, and xmlexception help needed

Rookie in need of some advice
I have searched but not real clear on some solutions. I'll do my best at explaining (and I apologize if I'm in the wrong newsgroup). We just added a 2nd server to our LAN. Problem is that the original DC DNS Suffix was a top level domain not the suggested "MAINOFFICE.LOCAL" Our old DC was used for authentication, terminal services and it did have Exchange 2000 installed and the main use of Exchange was to share contacts and a public calendar but the public folders after backing up last night seem fairly large Well due to growth and to avoid future problems (the DNS Suffi...

should i reformat hard drive?
I will be giving my sister my old xp computer. Should I reformat the hard drive before I give it to her or is there a better way to erase my footprints? -- Janice if you give her your laptop the windows license must also be give to her. so if you have the xp disks you should pass them on to her with the laptop. in regards to formatting, this is not ideal especially if you integrated third party software which you want her to have as well. instead erase your footprints or move your footprints onto a dvd or cd for future reference. --------------- on the other ...

Code Needed
I need to find where i can get some onsave command code. ie. when the save button is clicked then a msgbox would appear asking if you want to launch the external Quote program, if yes the it would point to the location of our quote program and launch it. Put this code in the OnSave event of your form. You will need to nodify to suite your needs. fso = new ActiveXObject("Scripting.FileSystemObject"); function RunProgram(Program_Path) { Default_Value = Program_Path.match(new RegExp("Default", "i")) if (Default_Value) { } else { new ActiveXObject(&q...

If/AND/OR help needed..
I'm having trouble with an IF,AND,OR formula... A4=08:00 B4=20:00 A10 is a time entry cell, such as 10:21 A11 is a date entry cell, such as 20/03/2005 A12 works out the day of the week according to A11 (=A11 formatted as "dddd") I need the following: - If the time entered in A10 is between A4 and B4 and the day of week is NOT Saturday or Sunday the the answer should be "PEAK". If it is Saturday or sunday, the answer should be "OFFPEAK" regardless of the time. 10:21 on 20/03/05(sun) should be OFFPEAK 10:21 on 21/03/05 (mon) should be PEAK 20:05 on 21/03...

Need macro
I have an Excel spreadsheet that is emailed to me each day. I have to make the same changes every time before I print. I am trying to consolidate these steps into a macro but can't seem to get it to work. I shrink the column widths for all columns (the largest of which is about 20 pixels wide). I do this so that when the spreadsheet prints on one page the type is not so small you I can't read it. Then I change the page setup to landscape, legal, and fit 1o 1 page. Is it even possible to make a macro to do this? Record a macro while you do it all manually. The...

Active drive and System drive
Yesterday I was looking at an HP Pavilion computer that suddenly stopped booting for a new client. Windows would start, flash a blue screen and restart. I loaded Bart PE and could view the data on both drives. I noticed that one smaller drive (Samsung) had Windows/Program Files/etc on it, and the 2nd larger drive (WDC) did also. The client had said someone else installed a new drive in the machine, which I assumed was the larger drive (I was wrong). So I switched the boot options to boot from the 2nd drive (Samsung), since there are two drives in the machine. Then XP MC l...

design help needed
Need some guidance on a task I am looking in to. I hava an excel spreadsheet that exports some data in to a access db table. There are 6 fields in the table (ID, MName, Role, Proj, Joined and Group. What I am trying to figure out is a way to clean up the table. In each field there are duplicates. I was thinking that I could create a separate table for the MNames and Proj fields. Then replacing the data with the key id from the new fields. Does this sound like I'm going in the right direction. Thanks Bob Bobbo, Well, we know what you have but not what you are doing....