Nested If Function to be converted into a user-defined custom function

Dear Experts:

below formula (nested if formula) WORKS FINE in EXCEL 2007, but it is
not working in 2003 for known reasons (number of conditions exceeding
the limit) .

I now would like to create a user-defined custom function (UDF) in
Excel 2003.

I know how to operate the VBA Editor but I got no idea how this nested
If-Formula translates into a VBA-code.

Could somebody please help me. Help is much appreciated. Thank you
very much in advance.

Regards, Andreas

------------------------------------------------------------------------------

Below formula (nested IF-Function) that WORKS fine in Excel 2007
should be translated into a VBA code for a custom-defined function for
Excel 2003


IF(F21="","",IF(ISERROR(VLOOKUP(LEFT(F21,Find(CHAR(10),F21)-1),'DocumentPath.xls'!
MatrixInd30,2,FALSE))=FALSE,VLOOKUP(LEFT(F21,Find(CHAR(10),F21)-1),'DocumentPath.xls'!
MatrixInd30,2,FALSE),IF(ISERROR(VLOOKUP(LEFT(F21,Find(CHAR(10),F21)-1),'DocumentPath.xls'!
MatrixInd100,2,FALSE))=FALSE,VLOOKUP(LEFT(F21,Find(CHAR(10),F21)-1),'DocumentPath.xls'!
MatrixInd100,2,FALSE),IF(ISERROR(VLOOKUP(LEFT(F21,Find(CHAR(10),F21)-1),'DocumentPath.xls'!
MatrixInd200,2,FALSE))=FALSE,VLOOKUP(LEFT(F21,Find(CHAR(10),F21)-1),'DocumentPath.xls'!
MatrixInd200,2,FALSE),IF(ISERROR(VLOOKUP(LEFT(F21,Find(CHAR(10),F21)-1),'DocumentPath.xls'!
MatrixInd300,2,FALSE))=FALSE,VLOOKUP(LEFT(F21,Find(CHAR(10),F21)-1),'DocumentPath.xls'!
MatrixInd300,2,FALSE),IF(ISERROR(VLOOKUP(LEFT(F21,Find(CHAR(10),F21)-1),'DocumentPath.xls'!
MatrixInd500,2,FALSE))=FALSE,VLOOKUP(LEFT(F21,Find(CHAR(10),F21)-1),'DocumentPath.xls'!
MatrixInd500,2,FALSE),IF(ISERROR(VLOOKUP(LEFT(F21,Find(CHAR(10),F21)-1),'DocumentPath.xls'!
MatrixNK,
2,FALSE))=FALSE,VLOOKUP(LEFT(F21,Find(CHAR(10),F21)-1),'DocumentPath.xls'!
MatrixNK,2,FALSE))))))))
0
andreashermle
5/18/2010 11:54:55 AM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
814 Views

Similar Articles

[PageSpeed] 24

Hello Andreas,

It would be helpful for understanding your problem if you could assign say 
cells: A1 to A10 to your partial results that come up nearly in each section 
of your formulae.
An explanation of how you use this components ( a verbal summary of the 
formulae) in your long IF statement would help anyone thinking about your 
problem.

best regards,

Gabor Sebo

"andreashermle" <andreas.hermle@gmx.de> wrote in message 
news:1db1560e-7f21-4f37-b885-9b77bc62871e@o39g2000vbd.googlegroups.com... 

0
helene
5/18/2010 6:23:56 PM
Reply:

Similar Artilces:

Receivable management user error
We have made a mistake and conducted our year-end receivable's management too early. Is anything affected if we run the year-end procedure for receivalbe's management again at the correct date and time? Thanks -- V.P. Operations From the on-line help: Closing the fiscal year updates information that is displayed in the Customer Summary window and Customer Yearly Summary Inquiry window when you select Amounts Since Last Close in the Summary View list in those windows. Closing the calendar year will not affect the information that is displayed in those windows. "Santiag...

Identify and email name of user
I have an Excel 2007 workbook used by several individuals. I need to send an email with the user's name whenever a user makes a change and saves the file. I have a working process that prompts the user to save the file when they close it and, if they choose yes, will send an email (based on Ron de Bruin's code.) However, I need a couple of enhancements: 1. Capture the name of the user and add it to the email. 2. If a user saves the file before closing it, trap that act and run the email routine before saving the file. I would prefer not to Share the file but if that is nec...

Creating Mailboxes works for some users, not for others
Ok, now I'm all confused. I have setup Exchange 2003 on a Windows 2003 server. I created my own Exchange mailbox by right clicking my username in the management console, then going to exchange tasks, create mailbox. Which worked fine. I can log in through Outlook 2003 (with Exchange server info entered in Mail under Control Panel) and through OWA. I have setup another user, which worked fine. At this point, any user setup in the exact same method beyond the second one doesn't work. Help? Any ideas appreciated... "Ken Montgomery" wrote: > Ok, now I...

Restrict Non-External mail users
We are in the process of moving mailboxes from Ex 5.5 to Ex 2003. We were told that there is no way to disable specific users from receiving external email, that every user must have the smtp record but that makes no sense. We have about 20 users that do not need exteranl mail as a part of their job function OR they have abused their privilgese in the past. With 5.5 we simply deleted the smtp record but with 2k3 smtp is required. Surely there is a way to disable this function. Do you use ISA 2000? "COB" <COB@discussions.microsoft.com> wrote in message news:A910483F-C...

Reverse MATCH Function
I have an array with 500+ elements. Assume elements 100, 200, 300, and 400 are equal to 'Sue'. For each occurrence of Sue, I want to find the row number for the next occurrence and for the previous occurrence. At 100, I get 200 and 'none'. At 200, I get 300 and 100. But when I get to 300, I get 400 and 100, altho I want 400 and 200. When I am at 300, is there some way to have the search start at 299 and search backwards so that the first match it comes to is at 200? -- Bill @ UAMS Hi! Not pretty but it works! I assume there are no blanks in the array and you wa...

OWA keeps asking for authentication over and over again... but only for 1 user
So, the first time my use goes to https://owa.domain.com/exchange, she has to put in her username (domain.com\name) and password. However, wheeven she goes to create a NEW email or Reply email, she is again asked to authenticate.... Any ideas why? It can happen if you modify from the IIS console the access rights of the exchange directories. This rights must be changed - if necesary - only from exchange system manager console, if not, the consistency is lost. check the permissions of the owa directories and set it to default permissions. You can find help in this article ID: 327843 -- ...

Former Quicken user can't balance account
I switched from Quicken to Money 2005 about six months ago, and I'm still pretty new at this. For the first time, I tried to balance my account today, starting with a bank statement from last January. I entered the statement date, starting and ending balances. Everything works fine, except I'm not sure I understand why Money complains about a large difference between my statement and the register. The balance in the register on the starting date of the statement roughly equals the starting balance of the statement. Same thing near the ending date -- the register and stateme...

Converting A Word Table To Excel
Hello to all - Is it possible to convert a table created in Word to an Excel spreadsheet? If so, please advise. I have a form that I created in Word that I would like to convert into an Excel Spreadsheet Thank you. In Word, select the table and copy it In Excel, select a cell and paste. Be aware that you will need to perform some modest re-formatting -- Gary's Student "The Inquirer" wrote: > > Hello to all - > > Is it possible to convert a table created in Word to an Excel spreadsheet? > If so, please advise. > I have a form that I created in Word t...

how do i set up a list of customers in excell for mail merge
I am looking to set up a data base of 100 customers. I need to be able to pull the data from the spreed sheet to make lables and to do a mail merge letter. What would be the best way to set that up useing word and excell Create mailing labels by merging an address list Step 1: Create the main document Click New Blank Document on the Standard toolbar. On the Tools menu, click Mail Merge. Under Main document, click Create, and then click Mailing Labels. Click Active Window. The active document becomes the main document. Step 2: Open or create the data source In the Mail Merge Helper...

Custom Colour palette
Hello all, I work in a company where there is this common excel file with a series of hyperlinks to other files within our file server. The problem is that when some users click on the hyperlinks and go back to the document where they used the hyperlinks the colours of the cells change to a generic gray colour for no reason whatsoever. They are all using Office 2003 and it happens at random. The latest patches have been installed and I have created copies of these files with no luck to test and compare against the original with no luck. Does anyone have any suggestions. Thank you ...

Call Same Function for multiple controls
I have a function that generates a SQL statement based on selections (checkboxes) the user makes on the form. I want to have that function be called whenever any of the checkboxes are changed (afterupdate event). I know that I can create 23 different AfterUpdate event Subs, one for each check box, and add the line to call the funciton to each sub. What I don't know is if I can set that function as the AfterUpdate in the control properties. So I don't have 4-5 lines of code nearly duplicated 23 times. For example, if I somehow was able to have the function as a macro, I could s...

Custom tabs on runaround text
I can't make custom tabs work on text which is flowing around the right side of a picture. The picture is on the left margin, text is aligned flush left. Tab moves the first line of the paragraph .5 no matter what I do. I set a tab at .25", tried setting one at 4.5" which is the actual position of the runaroud paragraph + .25", this should indent the first line of the paragraph .25. It still goes to + .5 Indent first line .25 doesn't work either. What do I need to do here? Bob Set the default tab stop to your preferred stop. The default is .5, you can set it to a...

Custom Forms
Our management is thinking about using Outlook to have contact data that is shared and synchronized with our sales personnel in the field using custom forms. What they have in mind would require custom forms that can deal with a master/detail type of relationship like you would have in an app tied to a relational database. Can Outlook custom forms even do this? I have not found any examples sounding remotely like this sort of thing. TIA -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com Outlook itself is not a relational database and, t...

2 users receive the same e-mail
Hi all ! I just have a "small" problem. I have 250 users in my org. Periodically, one of these users receive an automatic e-mail from one of our customers. What is strange is that another user receives this e-mail too but he isn't present in any field (to, cc, bcc). If I have a look at the logs in System Manager, I can see that this e-mail is sent to User1 but not to User2. In Outlook on the User1 PC there's no rule. I asked my provider and he tell me the same, just one mail is sent to User1. One more thing, this kind of e-mail is sent everyday to User1 but User2 doesn&...

Converting From An XP Computer To A Windows 7 Computer
My new Windows 7 laptop will likely have Windows Live Mail already installed. My existing XP laptop has WLM installed as well. How do I make sure that my local folders, contact list and email settings (I have two email addresses) are transferred from my old computer to my new one in a way that will immediately synch with the installed WLM software? Please be as specific as possible. Go ahead and install WLM on your XP machine now and set up everything there. That will make your transition to the new W7 machine easiest. http://download.live.com You can File - Export from OE (?) ...

Find distinct records of the last order from a customer
Hi I am trying to find a list of customers that have not bought anything for the last xx months. I have a salesorder table which holds an orderdate and customer account code, other customer details are held in the customers table. i have a table called soitemsdespatch thats holds the parts that have been sold to that customer. salesorder and soitemsdespatch are linked by sonumber. I was hoping that would show me the last orderdate for all customers and list them in order but it repeats customer orders but not all of them. What i would really like is one query that would show m...

User can't send an email to himself
Hello everyone, A user in my office has a strange problem. He cannot send an email to himself, nor does he get any emails sent to the "All Employees" group on our exchange server even though he is a member. The email gets sent ok, it is in his sent items folder, but he never gets it. He doesn't have any rules setup. I don't see the email in the queue on the server, I'm stumped. Any suggestions? We're running Office XP sp2 on an Exchange 5.5 sp4 server. Thanks! ...

converting web page files from publisher 2007 to word 2008
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) One of the reasons I got office 2008 for my new Mac was to retrieve my web files. well ta da, no ms publisher with office 2008. what do i do except start all over again. for sure not with office this time. With your new Mac, buy VMWare Fusion, install Windows 7, install your old Office for Windows and get on with it using Microsoft Publisher from there. --rms www.rmschneider.com plee@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) One of the > reasons I got of...

Custom Entities #2
Our company wants to track the amount of samples that we send out to our accounts, contacts & leads. I have gone in and created an entity called Samples. The next step is where I get confused - how do I create the relationships between accounts, contacts and leads so that the sample option shows up in all three entities? And is the relationship type a parental, refential, etc.? you will want to create N:N relationships between: Accounts and Samples Contacts and Samples Leads and Samples. Referential relationships should work fine. Dave Ireland "Wendy" <Wendy@discu...

how to add a activeX control to a custom entity
I want to add a activeX control in a custom entity. It should be added of load of the form. So i am trying to write a onload javascript function which adds the axtiveX control. I new to MSCRM 3.0. Please direct me how to do this. It deppends on what you exactly want to do. But if it is just a control you might want to use an IFrame a create a simple html page which embeds the ActiveX control. -- Patrick Verbeeten (MCSD) Lead Developer Aviva IT Web: aviva-it.nl "Prakash N" wrote: > I want to add a activeX control in a custom entity. It should be added of > load of ...

Overwriting custom fields in MS Project 2007
If I create resource custom fields in a project plan, that is only relevant to that specific project, how do I prevent the info from being overwritten - for enterprise resources - when I publish the plan to the PWA & then reopen it? Thanx Tracey Tracey -- Because of the way Microsoft has designed Project Server 2007, project managers are not allowed to specify ANY information about enterprise resources, even if you use a local Resource field to specify the information. Therefore, there is nothing you can do to change this and resolve your problem. Sorry. Hope this...

Countif with "AND"function
Hi I am working on this spreadsheet, where I have 2 columns. The first column has a numerical value, in a random order (ranging from 1-6) and the second column had alphabets again in a random order (ranging from A-F). This what I am trying to do I need a count of number of 3's (first column) which have a value of F So, in effect there are 2 searches, first one in column one is looking for the number 3 and the second one is looking for the letter F in column two, with the combination that these letter F's should also have a value of 3 in column one as well Need help ASAP Thanks ...

How do I convert text to uppercase?
How do I convert all text in an existing Excel sheet that contains both upper and lower case letters? If a1 is upper then =upper(a1) will return ( UPPER) =lower will return all lower case =proper will capitalize first letter of each wor -- SilverM7 ----------------------------------------------------------------------- SilverM78's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1463 View this thread: http://www.excelforum.com/showthread.php?threadid=26264 See my web page http://www.mvps.org/dmcritchie/excel/proper.htm --- HTH, David McRitchie, Microsoft MV...

unable to enable the flag function on outlook 2003
I followed the 'help' instructions to enable the flagging function in outlook and have the flag column show; but I was not able to choose that option (under other options, it was grey) ? What type of email account do you have? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM You can access this newsgroup by...

Nested IF #4
A little bird told me that in Excel12 the maximum number of nested IF statements will increase from 7 to 64 making it possible to write a really scary cell formula. Is Microsoft encouraging sloppy worksheet practice? Ed Ferrero http://www.edferrero.com Don't know about any little birds - you can get the straight scoop from http://blogs.msdn.com/excel/default.aspx where indeed MS announced that nesting is increased to 64 levels. MS isn't encouraging anything in terms of worksheet practice. It's responding to demands from the purchasers of their product. There are any...