Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with formulas???
Hi,
I am pretty new to Excel. I want to be able to import data from a number of client trading reports and the client's account number needs to be converted into an advisor's name. Say I have 20 advisors, and each advisor has 50 clients, I need to get excel to put the appropriate adviser's name next their client's account number if it appears in column A. For example, if I import the below data, I would like to create a formula which says "if A1 = U11111 then enter the word "Megan" appears in B2", "if A1 = u11112 then enter the word "Jane" in B2" and so on. I will need to create about 1000 if statements for each row in the column A, which will result in a different name in column B. A1 U11111 B1 *advisor's name* A2 U11112 B2 *advisor's name* A3 U39393 B3 *advisor's name* A4 U23893 B4 *advisor's name* A5 U23930 B5 *advisor's name* A6 U39290 B6 *advisor's name* and so on... Your help is greatly appreciated. Natasha |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with formulas???
1. Create the unique list in a separate sheet say Sheet2 with the valid codes
in ColA and corresponding names in ColB 2. In your current sheet say Sheet1 in B1 use this formula =VLOOKUP(A1,Sheet2!A:B,2,FALSE) If this post helps click Yes --------------- Jacob Skaria "Natasha" wrote: Hi, I am pretty new to Excel. I want to be able to import data from a number of client trading reports and the client's account number needs to be converted into an advisor's name. Say I have 20 advisors, and each advisor has 50 clients, I need to get excel to put the appropriate adviser's name next their client's account number if it appears in column A. For example, if I import the below data, I would like to create a formula which says "if A1 = U11111 then enter the word "Megan" appears in B2", "if A1 = u11112 then enter the word "Jane" in B2" and so on. I will need to create about 1000 if statements for each row in the column A, which will result in a different name in column B. A1 U11111 B1 *advisor's name* A2 U11112 B2 *advisor's name* A3 U39393 B3 *advisor's name* A4 U23893 B4 *advisor's name* A5 U23930 B5 *advisor's name* A6 U39290 B6 *advisor's name* and so on... Your help is greatly appreciated. Natasha |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with formulas???
Previous response from Jacob is spot on.
Only I'd suggest using absolute values for your lookup. So for example if your table on Sheet2 goes from row 2 to row 150 use =VLOOKUP(A1,Sheet2!$A$2:$B$150,2,FALSE) Jacob's way will work absolutely fine though but my version will allow you to use other cells in columns A and B of Sheet2 without getting false data reporting back. Kiss, kiss. "Jacob Skaria" wrote: 1. Create the unique list in a separate sheet say Sheet2 with the valid codes in ColA and corresponding names in ColB 2. In your current sheet say Sheet1 in B1 use this formula =VLOOKUP(A1,Sheet2!A:B,2,FALSE) If this post helps click Yes --------------- Jacob Skaria "Natasha" wrote: Hi, I am pretty new to Excel. I want to be able to import data from a number of client trading reports and the client's account number needs to be converted into an advisor's name. Say I have 20 advisors, and each advisor has 50 clients, I need to get excel to put the appropriate adviser's name next their client's account number if it appears in column A. For example, if I import the below data, I would like to create a formula which says "if A1 = U11111 then enter the word "Megan" appears in B2", "if A1 = u11112 then enter the word "Jane" in B2" and so on. I will need to create about 1000 if statements for each row in the column A, which will result in a different name in column B. A1 U11111 B1 *advisor's name* A2 U11112 B2 *advisor's name* A3 U39393 B3 *advisor's name* A4 U23893 B4 *advisor's name* A5 U23930 B5 *advisor's name* A6 U39290 B6 *advisor's name* and so on... Your help is greatly appreciated. Natasha |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
formulas for changing formulas? | Excel Discussion (Misc queries) |