Unsure of the Function, and How to Write It
This may be a very basic question, so excuse my ignorance!
I have developed a Data Validation setting that refers to a list (Titles): Mr Mrs Miss Ms Dr I have this list on a separate sheet (Lists). On Sheet 1, I want to have the data entry team select from the list. That is working fine, in column C of Sheet1. My database requires that the above titles all be converted to numbers: 1 for Mr, 2 for Mrs and so on, with 5 for Dr. I actually need to import both the text, and the number. I'm trying to automate the process so that when Mr is selected from the drop down menu in column C, then the number 1 will appear in column B. When Mrs is selected from the drop down menu in column C, then the number 2 will appear in column B. On the Lists spreadsheet I had created a column adjacent to the Titles fields with 1 through 5. I thought I would be able to somehow have the cells on Sheet 1 use the Titles and the numbers area from the Lists sheet, but I can't get it to work. Can someone pls advise me how I can achieve this? I am very new to this type of Excel work, so would appreciate the simplest explanation possible! Thanks, |
Unsure of the Function, and How to Write It
in B2:
=VLOOKUP(C2,Lists!A:B,2,0) assuming your Titles & Numbers are in Lists, columns A & B HTH "Jane" wrote: This may be a very basic question, so excuse my ignorance! I have developed a Data Validation setting that refers to a list (Titles): Mr Mrs Miss Ms Dr I have this list on a separate sheet (Lists). On Sheet 1, I want to have the data entry team select from the list. That is working fine, in column C of Sheet1. My database requires that the above titles all be converted to numbers: 1 for Mr, 2 for Mrs and so on, with 5 for Dr. I actually need to import both the text, and the number. I'm trying to automate the process so that when Mr is selected from the drop down menu in column C, then the number 1 will appear in column B. When Mrs is selected from the drop down menu in column C, then the number 2 will appear in column B. On the Lists spreadsheet I had created a column adjacent to the Titles fields with 1 through 5. I thought I would be able to somehow have the cells on Sheet 1 use the Titles and the numbers area from the Lists sheet, but I can't get it to work. Can someone pls advise me how I can achieve this? I am very new to this type of Excel work, so would appreciate the simplest explanation possible! Thanks, |
Unsure of the Function, and How to Write It
Thanks Toppers. It worked, and I applied it to 2 other areas. I don't
really understand it, but I have read the Help on Vlookup, and have a little bit of a grasp of what is happening! Much appreciated. Jane "Toppers" wrote: in B2: =VLOOKUP(C2,Lists!A:B,2,0) assuming your Titles & Numbers are in Lists, columns A & B HTH "Jane" wrote: This may be a very basic question, so excuse my ignorance! I have developed a Data Validation setting that refers to a list (Titles): Mr Mrs Miss Ms Dr I have this list on a separate sheet (Lists). On Sheet 1, I want to have the data entry team select from the list. That is working fine, in column C of Sheet1. My database requires that the above titles all be converted to numbers: 1 for Mr, 2 for Mrs and so on, with 5 for Dr. I actually need to import both the text, and the number. I'm trying to automate the process so that when Mr is selected from the drop down menu in column C, then the number 1 will appear in column B. When Mrs is selected from the drop down menu in column C, then the number 2 will appear in column B. On the Lists spreadsheet I had created a column adjacent to the Titles fields with 1 through 5. I thought I would be able to somehow have the cells on Sheet 1 use the Titles and the numbers area from the Lists sheet, but I can't get it to work. Can someone pls advise me how I can achieve this? I am very new to this type of Excel work, so would appreciate the simplest explanation possible! Thanks, |
Unsure of the Function, and How to Write It
Debra Dalgleish has lots of notes on =vlookup():
http://www.contextures.com/xlFunctions02.html Jane wrote: Thanks Toppers. It worked, and I applied it to 2 other areas. I don't really understand it, but I have read the Help on Vlookup, and have a little bit of a grasp of what is happening! Much appreciated. Jane "Toppers" wrote: in B2: =VLOOKUP(C2,Lists!A:B,2,0) assuming your Titles & Numbers are in Lists, columns A & B HTH "Jane" wrote: This may be a very basic question, so excuse my ignorance! I have developed a Data Validation setting that refers to a list (Titles): Mr Mrs Miss Ms Dr I have this list on a separate sheet (Lists). On Sheet 1, I want to have the data entry team select from the list. That is working fine, in column C of Sheet1. My database requires that the above titles all be converted to numbers: 1 for Mr, 2 for Mrs and so on, with 5 for Dr. I actually need to import both the text, and the number. I'm trying to automate the process so that when Mr is selected from the drop down menu in column C, then the number 1 will appear in column B. When Mrs is selected from the drop down menu in column C, then the number 2 will appear in column B. On the Lists spreadsheet I had created a column adjacent to the Titles fields with 1 through 5. I thought I would be able to somehow have the cells on Sheet 1 use the Titles and the numbers area from the Lists sheet, but I can't get it to work. Can someone pls advise me how I can achieve this? I am very new to this type of Excel work, so would appreciate the simplest explanation possible! Thanks, -- Dave Peterson |
All times are GMT +1. The time now is 02:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com