Multiple IF statement replacements?

I need some help and don't know if it will be possible to get it to do
what I need. I am automating all my paperwork I do every morning and I
need a cell on a worksheet to decypher people's initials and translate
it to a name. here is a sample of what I have.

=IF('INDIVIDUAL TARGET'!A21="DH","Diane",IF('INDIVIDUAL
TARGET'!A21="LT","Lisa",IF('INDIVIDUAL
TARGET'!A21="LB","Linda",IF('INDIVIDUAL
TARGET'!A21="RN","Rae",IF('INDIVIDUAL
TARGET'!A21="LM","Liz",IF('INDIVIDUAL
TARGET'!A21="KC","Kathie",IF('INDIVIDUAL
TARGET'!A21="GL","Gladys",IF('INDIVIDUAL TARGET'!A21="DC","Dorothy","
"))))))))

But I hit the seven IF limit. I have a maxium of 18 workers. The table
that lists the intials are or a seperate worksheet within the file and
are in a single column.

I know this probably looks messed up. but any help will be
appreciated.

Thanks,
Steven J


---
Message posted from http://www.ExcelForum.com/

0
1/2/2004 5:41:03 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
575 Views

Similar Articles

[PageSpeed] 25

use VLOOKUP

with your initials in say A1:A20 and the corresponding names in B1:B20 and your
lookup value in A21, use:-

=VLOOKUP(A21,A1:B20,2,0)

This will look for whatever value is in A21, and it will look for it in the
leftmost column of the range A1:B20, and when it finds it it will return the
corresponding data from the 2nd column in that range.  the last and 4th optional
argument of 0 menas it must find an exact match for it to return anything (There
are times when close enough will do).

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
Best wishes to all, and hope for a good New year  :-)
----------------------------------------------------------------------------



"SJ316 >" <<SJ316.zf10y@excelforum-nospam.com> wrote in message
news:SJ316.zf10y@excelforum-nospam.com...
> I need some help and don't know if it will be possible to get it to do
> what I need. I am automating all my paperwork I do every morning and I
> need a cell on a worksheet to decypher people's initials and translate
> it to a name. here is a sample of what I have.
>
> =IF('INDIVIDUAL TARGET'!A21="DH","Diane",IF('INDIVIDUAL
> TARGET'!A21="LT","Lisa",IF('INDIVIDUAL
> TARGET'!A21="LB","Linda",IF('INDIVIDUAL
> TARGET'!A21="RN","Rae",IF('INDIVIDUAL
> TARGET'!A21="LM","Liz",IF('INDIVIDUAL
> TARGET'!A21="KC","Kathie",IF('INDIVIDUAL
> TARGET'!A21="GL","Gladys",IF('INDIVIDUAL TARGET'!A21="DC","Dorothy","
> "))))))))
>
> But I hit the seven IF limit. I have a maxium of 18 workers. The table
> that lists the intials are or a seperate worksheet within the file and
> are in a single column.
>
> I know this probably looks messed up. but any help will be
> appreciated.
>
> Thanks,
> Steven J
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.557 / Virus Database: 349 - Release Date: 30/12/2003


0
ken.wright (2489)
1/2/2004 5:54:03 PM
Steven,

You need a lookup table

Example....
For this example, the formulas are located on sheet1
There's a sheet named "Target" with A21 = LT
There's a sheet named "List" where I put the
lookup table. These can all be moved if referenced
correctly.
Okay...in the sheet labeled List, I entered the following
starting from A1 to B3 (you can expand this list as much
as you need to).
Col A    Col B
DH        Dianne
LT        Lisa
LB        Linda

Now from anywhere in sheet, I can use the following
formula:

=VLOOKUP(Target!A21,List!A1:B3,2,FALSE)
The result will be "Lisa".

For more help, check Excels help for "Lookup"

John




"SJ316 >" <<SJ316.zf10y@excelforum-nospam.com> wrote in message
news:SJ316.zf10y@excelforum-nospam.com...
> I need some help and don't know if it will be possible to get it to do
> what I need. I am automating all my paperwork I do every morning and I
> need a cell on a worksheet to decypher people's initials and translate
> it to a name. here is a sample of what I have.
>
> =IF('INDIVIDUAL TARGET'!A21="DH","Diane",IF('INDIVIDUAL
> TARGET'!A21="LT","Lisa",IF('INDIVIDUAL
> TARGET'!A21="LB","Linda",IF('INDIVIDUAL
> TARGET'!A21="RN","Rae",IF('INDIVIDUAL
> TARGET'!A21="LM","Liz",IF('INDIVIDUAL
> TARGET'!A21="KC","Kathie",IF('INDIVIDUAL
> TARGET'!A21="GL","Gladys",IF('INDIVIDUAL TARGET'!A21="DC","Dorothy","
> "))))))))
>
> But I hit the seven IF limit. I have a maxium of 18 workers. The table
> that lists the intials are or a seperate worksheet within the file and
> are in a single column.
>
> I know this probably looks messed up. but any help will be
> appreciated.
>
> Thanks,
> Steven J
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
jwilson (359)
1/2/2004 6:02:25 PM
Steven,

If you've got a separate table listing the initials, you can use a
VLOOKUP to retrieve the name given the initials.  If it isn't already
set up in the following manner, change it to match the format.
(columns A & B on sheet "INITIALS" for example):
DH    Diane
LB     Linda
RN    Rae
LN     Liz
KC    Kathie
etc...

Now use the formula

=VLOOKUP('INDIVIDUAL TARGET'!A21,INITIALS!A1:B20,2,FALSE)

Search VLOOKUP in the Help menu to get an idea of how the
function works.

Other possibilities for lookups are INDEX/MATCH and LOOKUP

Dan E

"SJ316 >" <<SJ316.zf10y@excelforum-nospam.com> wrote in message news:SJ316.zf10y@excelforum-nospam.com...
> I need some help and don't know if it will be possible to get it to do
> what I need. I am automating all my paperwork I do every morning and I
> need a cell on a worksheet to decypher people's initials and translate
> it to a name. here is a sample of what I have.
>
> =IF('INDIVIDUAL TARGET'!A21="DH","Diane",IF('INDIVIDUAL
> TARGET'!A21="LT","Lisa",IF('INDIVIDUAL
> TARGET'!A21="LB","Linda",IF('INDIVIDUAL
> TARGET'!A21="RN","Rae",IF('INDIVIDUAL
> TARGET'!A21="LM","Liz",IF('INDIVIDUAL
> TARGET'!A21="KC","Kathie",IF('INDIVIDUAL
> TARGET'!A21="GL","Gladys",IF('INDIVIDUAL TARGET'!A21="DC","Dorothy","
> "))))))))
>
> But I hit the seven IF limit. I have a maxium of 18 workers. The table
> that lists the intials are or a seperate worksheet within the file and
> are in a single column.
>
> I know this probably looks messed up. but any help will be
> appreciated.
>
> Thanks,
> Steven J
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
1/2/2004 6:03:50 PM
Reply:

Similar Artilces:

Multiple Countif Sums
I have 2 calculation to find out, one that finds all cells in K:K that have a time equal to or less than 4:00:00 (4 o clock) and the other that finds the ones that are equal to or over 4:00:01. Obviously that is easy enough but I only want to include the ones that have a 1 in the corresponding G:G column. I have has a search around on here and gone through several sums using countif, sumproduct, count(if but none are giving out the correct figures. Can anyone assist? Thank you in advance Use SUMPRODUCT. But SUMPRODUCT does not accept whole columns frerences like G:G. If you want tu su...

Multiple SMTP Addresses
We've had trouble getting e-mail to route between our 2 Exchange servers internally. Each server is in its own administrative group and we have installed the routing connector for each group. Recently, we've added a new alias for mail. The external MX record has been added, and the A record will point to the external address in a new location (old location will retain external SMTP mail for a different SMTP address). My question is: how do I create a recipient policy to allow for the new SMTP alias (as primary) separate from the policy used for the other location? (They have a differ...

same options for multiple charts
I have monthly pie charts which are in separate sheets that are updated at the end of the month. I would like each pie slice to maintain consistency with color throughout the months. How do I go about having the same options for every single pie chart I create without having to invidiviually go in and change each chart every month? I also have a YTD pie chart which I would like to be updated every time I add data to the new month's chart. Is there a way this can be automatically updated? PLEASE HELP! Thanks!! What kind of options? What changes about the data or chart that cause...

Replacing a node
Hi all: Is there a way to replace a node and it's children? I tried using the ReplaceChild method but that seems to only replace the first child only!! Thanks Ed;; Ed A wrote: > Is there a way to replace a node and it's children? I tried using the > ReplaceChild method but that seems to only replace the first child only!! node.ParentNode.ReplaceChild(newNode, node) will replace node with newNode. If you still have problems then provide more details on what you have tried. -- Martin Honnen --- MVP XML http://JavaScript.FAQTs.com/ This is what I've tried: M...

multiple criteria
I am trying to create queries to sort church membership data according to any number of ministry groups (out of a possible 56) they volunteer for. My goal is to create a report for each of the Ministry's with name and contact information. I have some questions with corresponding database description information that will hopefully enable an expert to point me in the right direction... Questions: (1) I am at the query step and am looking for a way to link up the ministry with the corresponding FName. I have entered a ministry name in the criteria cells (e.g., "Choir") for all 28 c...

macro to Find Replace in Excel
Hi, I need to find double quotes in an excel file (could be a selectoin of rows) and replace them with nothing ( yeah just want to delete them) The built in Excel function to Find and Replace all gives me error "formula too long" Can someone please write me a small macro code thanks Hi I just tried with Find and Replace Replace All " with nothing entered removes all " from sheet/range Replace All "" with nothing entered removes all "" from sheet/range, but entries like " or " " remain unchanged. Arvi Laanemets "Nurddin&quo...

Problem using Find and Replace ,B with ,C within an Excel Formula
Hi, I am trying to replace ,B with ,C in the following formula and get an incorrect result. =SUMPRODUCT(BB$2:BB$377,BL$2:BL$377)/(SUMPRODUCT(BB$2:BB$377,BO$2:BO$377)+SUMPRODUCT(BB$2:BB$377,BG$2:BG$377)+SUMPRODUCT(BB$2:BB$377,BH$2:BH$377)+SUMPRODUCT(BB$2:BB$377,BI$2:BI$377)+SUMPRODUCT(BB$2:BB$377,BJ$2:BJ$377)+SUMPRODUCT(BB$2:BB$377,BK$2:BK$377)+SUMPRODUCT(BB$2:BB$377,BL$2:BL$377)+SUMPRODUCT(BB$2:BB$377,BM$2:BM$377)+SUMPRODUCT(BB$2:BB$377,BN$2:BN$377)) When I do a find and replace of ,B with ,C Excel replaces the :B with :C instead of replacing ,B with ,C . See the result below after the fin...

Multiple Modeless Dialogs
I have multiple modeless dialogs as children in a CView window. I wish to arrange these in the view, but can't quite figure out how to get the size of each one and convert this into the sizes used in the parent dialog. If anyone could point me in the right direction it would be greatly appreciated. Thanks in advance, Charles What does this mean? You can get the size using GetWindowRect/GetClientRect etc. But I dont follow what you are going to do with moving them within a view etc. --------- Ajay Kalra ajaykalra@yahoo.com Charles wrote: > I have multiple modeless dialogs as ch...

dynamic reference to data in multiple closed workbooks
Hi, I'm working with a job list (generated out of a different program) and data in associated closed workbooks. For example, the job list has names like HD-100311-TA031110, SHT-100312, 032110Mag and I can generate a list of these jobs along with other information I need. For every job there's an associated workbook and, without opening the workbook I want to pull, for example, '[\\de-mt1\clients\QC Reports\Proofs Adjusted\HD-100311-TA031110 proof adjusted.xls]'Upload'!A$2 where the only information that will change is the job name. I'm novice enough not to...

Multiple list boxes
In my form design, I have 3 list boxes in a form. List box 1 is created from a query, and is unbound. List box 2 is set to get whatever I selected from box 1. These two boxes work, without problems. Now I run into troubles trying to get list box 3 to get information from what I selected from list box 2. The criteria for list box 3 is [forms]![formname]![listbox2]...please help. Thank you. Andy, What happens with List3? Do you get too many records, too few, wrong records, etc...? Please indicate sample values, and your filtering fields and criteria from List1 through List3. -- ...

Overtime Calculation based on multiple hourly pay codes.
The system presently is just not designed to calculate overtime based on multiple hourly pay codes. If you enter 20 hours in one hourly paycode and 25 hours worked in another, these would all need to be entered for the same hourly pay code in order for 5 overtime hours to auto-calculate. The system should have the ability to check all the hourly paycodes for a given pay-period and then automatically calculate overtime if need be. Thanks Girish ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this s...

Deleting rows based on IF statement
How can I delete a row based on an IF statement? For example, if cell h2=C and if cell k2 = zero, then delete that row. Thank you. I would apply Data|Filter|Autofilter to the whole range. Then I'd filter on column H to show "C" Then I'd filter on column K to show 0 Then delete those visible rows Then Data|Filter|show all to see everything left. simmerdown wrote: > > How can I delete a row based on an IF statement? > > For example, if cell h2=C and if cell k2 = zero, then delete that row. > > Thank you. -- Dave Peterson ...

Multiple Interest Rates on the Same Credit Card Account
Has anyone figured out how to differentiate between different interest rates (i.e. Purchases at 10%, Cash Advances at 20%) in Microsoft Money 2002? Help! The debt reduction planner is not all that helpful with out this key piece of information!! ...

Multiple OL2000 processes running, none visible
I've seen this question asked in Usenet archives, but I haven't found a KB article or any definitive explanation on this. Here's the problem I have with a couple of users running Outlook 2000 on Windows 2000 Professional. Both are up-to-date on patches. They both launch Outlook from an icon in the Quick Launch folder; while troubleshooting this problem, I changed that icon to be a "regular" shortcut -- in other words, OL is launched with no command line parameters. Sometimes, they will click on the icon and "nothing happens." There is no "task" di...

Move multiple folders
Is there a way to move several folders (within the folder list) at once? anonymous@discussions.microsoft.com <anonymous@discussions.microsoft.com> wrote: > Is there a way to move several folders (within the folder > list) at once? Nope. -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapids, MI 49512-1991 Brian.Tillman is the name, smiths-aerospace.com is the domain. I don't speak for Smiths, and Smiths doesn't speak for me. ...

Link contact to multiple accounts
In MSCRM a contact can have only one account. How can I link contact to multiple accounts ? I have a contact person who is 1. VP of Eng of company A 2. Board of director of company B 3. Board of director of company C How do I store this information ? How do I customize ? Do I create a relation ? Any thoughts ? Arch. You could use the Powerrelate feature of Powertrak Core from Axonom, Inc. This is an ISV MS CRM add-on that does exactly that, and more. See www.axonom.com Peter "Arch" <discussion11@yahoo.com> wrote in message news:b87a74db.0405101222.52422bb@posting.google.c...

Multiple distribution points in config.xml
Anyone have an example of a config.xml file for Office 2007 with multiple distribution points? I currently have Office 2007 deployed but with only one network location and I would like to add additional ones for backup via a config.xml but I haven't found one example or a single bit of documentation on multiple points via a config.xml. ...

newb question
Hi all, new Outlook 2003 user here with a problem. Here's the issue: I have 2 email accounts in my life, a hotmail account, and my work email account. I've set them both up as seperate email accounts in Outlook, and both work just fine. But here's the rub: In order to connect to my work email account (which is set-up as an exchange server email account) I have to first connect using VPN software on my PC. I only really want to check my work email once or twice a month, so therefore I don't want to fire-up my VPN software every time I open Outlook. My hotmail, on the other...

Changing dates across multiple worksheets
I have a workbook that contains 18 sheets. Is it possible to change the date on sheet 1 and have it change on the other 17 sheets, even though the sheets contains two different date formats, 5/25, and 5/25/10, and all of the sheets are different than sheet 1? I’m using Excel 2007. Thanks, Malcolm Link each of the date cells on 17 sheets back to the main sheet where you will change the date. =Main!A1 where A1 is the changing date. Each sheet formatted to your choice. Gord Dibben MS Excel MVP On Tue, 25 May 2010 08:38:01 -0700, Malcolm <Malcolm@discussions.mic...

multiple copies of contacts after sync, and only one category is showing in address book
I have a user who after synching contacts with his axim has 3 copies of each - how can I remove the unecessary ones? Also he has his contacts separated into categories and only one shows in the address book - how can I get them all to show? thanks in advance for your help. mleon he is using outlook 2003 michele.removeleon@toyoda-na.com <michele.removeleon@toyoda-na.com> wrote: > I have a user who after synching contacts with his axim has 3 copies > of each - how can I remove the unecessary ones? Select the duplicates and click Delete. > Also he has his contacts separated...

Data validation for Multiple columns
Hi all, I am creating a spreadsheet which will have data entry from different users. I want to give the validation in such a way that a combination of values in 2 columns do not occur more than once. Like say A B 1 a 1 b 2 a 2 b 2 a in above example the combination 2 and a is recurring. it should be restricted by validation while entering the data itself. can anybody please help Regards NC Hi, sorry for bothering you all but i got a solution on other group here is link for others reference http://groups-beta.google.com/group/microsoft.public.excel.programming...

Outlook 2000
My client sends one email and recipients receive multiple copies.Has = anyone seen this before and can shed some light. >-----Original Message----- >My client sends one email and recipients receive multiple copies.Has anyone seen this before and can shed some light. >. >having the same trouble with 2003, are you using a remote pop3 server? >-----Original Message----- > >>-----Original Message----- >>My client sends one email and recipients receive >multiple copies.Has anyone seen this before and can shed >some light. >>. >>having the sam...

multiple database
I have 2 access database, both with a table called Score (that has names and grades as fields). Can I query from a third access database to have the average of grades from the 2 access database? Many thanks Dan YES, you can. \ \ \ \ Oh, you want a solution. Does the third database have links to the score tables in the other 2 databases? SELECT [Name], [Grade] FROM TableScores1 UNION ALL SELECT [Name], [Grade] FROM TableScores2 SELECT [Name], Avg([Grade]) FROM SavedUnionQuery GROUP BY [Name] Now with that UNION query as the source you can build a totals query. If the third database d...

calculating time with IF statement
I have the following: A1 is formated as time = 7:30 AM A2 is formated as time = 6:00 PM A3 is formated as time and contains =sum(A2 - A1) and gives 10:30 That part I've got down. Now, I want to subtract 30 minutes so I added: A4 is formated as time and contains =sum(A3-30/1440) and gives 10:00 Worked, thanks to those that have posted these questions. Now the problem: I only want to subtract the 30 minutes if cell A3 is greater than 7:00 How do I write that IF statement? Thanks for the help, Mark You could have used: =a2-a1 =a3-30/1400 =sum() doesn't really add anything to...

If Statement Help #4
Dear JulieD: I am very grateful for your help, If I understand your sugestion, would need to write a VBA macro to cut and paste-special the capture data into a new column? You would think that excel would provide a more elegant manor t capture volitale data when it meets a condition that is defined by formula. Capture being the operative word here. Is there another function other than "IF" that would be mor appropiate? Is there an email address and I will send you a copy o STOCKEVULATE.exl Thanks again, Michae -- MJSlatter -------------------------------------------------...