| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 - formula to display text from anoher worksheet
I have 2 worksheets. On sheet 1 is a list of items on order sorted by
customer number. The other sheet is a list of customer numbers in column A and coresponding names in column B. I want to display the customer name instead of the customer number in sheet 1 from the coresponding name in column B that matches the number in column A in sheet 2. What formula can I use for this? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000 - formula to display text from anoher worksheet
With
Sheet1!A1: (a CustNum) and Sheet2!A1:B100 containing the XRef list of CustNum/Name Try this on Sheet1: This formula returns the name associated with the CustNum B1: =VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0) Note: if an incorrect CustNum is in Col_A, the formula returns an error. If you need error checking, try this formula (in sections for readability): B1:=IF(ISNUMBER(MATCH(A1,Sheet2!$A$1:$A$100,0)), VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0),"No match") Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Scott J" <Scott wrote in message ... I have 2 worksheets. On sheet 1 is a list of items on order sorted by customer number. The other sheet is a list of customer numbers in column A and coresponding names in column B. I want to display the customer name instead of the customer number in sheet 1 from the coresponding name in column B that matches the number in column A in sheet 2. What formula can I use for this? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000 - formula to display text from anoher worksheet
The first formula works but I didn't realize I have first and last names.
Column B on sheet 2 contains the last names and column C on sheet 2 contains first names. How can I make it so another column in sheet 1 does the same thing with the first names from column C on sheet 2? I tried tweaking it but am having a hard time figuring it out. Thanks "Ron Coderre" wrote: With Sheet1!A1: (a CustNum) and Sheet2!A1:B100 containing the XRef list of CustNum/Name Try this on Sheet1: This formula returns the name associated with the CustNum B1: =VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0) Note: if an incorrect CustNum is in Col_A, the formula returns an error. If you need error checking, try this formula (in sections for readability): B1:=IF(ISNUMBER(MATCH(A1,Sheet2!$A$1:$A$100,0)), VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0),"No match") Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Scott J" <Scott wrote in message ... I have 2 worksheets. On sheet 1 is a list of items on order sorted by customer number. The other sheet is a list of customer numbers in column A and coresponding names in column B. I want to display the customer name instead of the customer number in sheet 1 from the coresponding name in column B that matches the number in column A in sheet 2. What formula can I use for this? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000 - formula to display text from anoher worksheet
Try this:
Last Names: B1: =VLOOKUP(A1,Sheet2!$A$1:$C$100,2,0) First Names: C1: =VLOOKUP(A1,Sheet2!$A$1:$C$100,3,0) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Scott J" wrote in message ... The first formula works but I didn't realize I have first and last names. Column B on sheet 2 contains the last names and column C on sheet 2 contains first names. How can I make it so another column in sheet 1 does the same thing with the first names from column C on sheet 2? I tried tweaking it but am having a hard time figuring it out. Thanks "Ron Coderre" wrote: With Sheet1!A1: (a CustNum) and Sheet2!A1:B100 containing the XRef list of CustNum/Name Try this on Sheet1: This formula returns the name associated with the CustNum B1: =VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0) Note: if an incorrect CustNum is in Col_A, the formula returns an error. If you need error checking, try this formula (in sections for readability): B1:=IF(ISNUMBER(MATCH(A1,Sheet2!$A$1:$A$100,0)), VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0),"No match") Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Scott J" <Scott wrote in message ... I have 2 worksheets. On sheet 1 is a list of items on order sorted by customer number. The other sheet is a list of customer numbers in column A and coresponding names in column B. I want to display the customer name instead of the customer number in sheet 1 from the coresponding name in column B that matches the number in column A in sheet 2. What formula can I use for this? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000 - formula to display text from anoher worksheet
Thank You Ron, That did it. I appreciate your time.
Scott "Ron Coderre" wrote: Try this: Last Names: B1: =VLOOKUP(A1,Sheet2!$A$1:$C$100,2,0) First Names: C1: =VLOOKUP(A1,Sheet2!$A$1:$C$100,3,0) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Scott J" wrote in message ... The first formula works but I didn't realize I have first and last names. Column B on sheet 2 contains the last names and column C on sheet 2 contains first names. How can I make it so another column in sheet 1 does the same thing with the first names from column C on sheet 2? I tried tweaking it but am having a hard time figuring it out. Thanks "Ron Coderre" wrote: With Sheet1!A1: (a CustNum) and Sheet2!A1:B100 containing the XRef list of CustNum/Name Try this on Sheet1: This formula returns the name associated with the CustNum B1: =VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0) Note: if an incorrect CustNum is in Col_A, the formula returns an error. If you need error checking, try this formula (in sections for readability): B1:=IF(ISNUMBER(MATCH(A1,Sheet2!$A$1:$A$100,0)), VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0),"No match") Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Scott J" <Scott wrote in message ... I have 2 worksheets. On sheet 1 is a list of items on order sorted by customer number. The other sheet is a list of customer numbers in column A and coresponding names in column B. I want to display the customer name instead of the customer number in sheet 1 from the coresponding name in column B that matches the number in column A in sheet 2. What formula can I use for this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ExcelExcel 2002 display formula in another cell as text | Excel Worksheet Functions | |||
| Display Problems in Excel 2000 | Setting up and Configuration of Excel | |||
| Stem-and-leaf display\Excel 2000 | New Users to Excel | |||
| Need Workaround for Cell Display Limitation in Excel 2000 | Excel Discussion (Misc queries) | |||
| Display excel formula on worksheet | Excel Worksheet Functions | |||
Hybrid Mode
