Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validating a name without using the Data Validation function
I am working in two workbooks. One workbook contains data that need to be
populated into the second workbook. I want to be able to input a person's last name into the second workbook and have it automatically retrieve the data (first name and manager's first & last name) from the first workbook and populate itself with the data. I am able to do all that but I have a problem when it come to people that have the same last name or the same first and last name. It searches the first workbook and pulls the data for the first match, it will not search past the first match. The only way to differentiate between the people is by their manager's name (hoping they have different managers). There are key identifiers but the people using the second workbook would not know what they are, so they cannot use them. Normally the first part of the problem could be resolved by using the Data- Validation method. For the purpose of this project I cannot use the Data- Validation method. The Data-Validation requires the first workbook to open or be a second worksheet in the second workbook. I don't want that to happen because of maintenance and the second workbook will be used by an entire dept and can be used as a template for other workbooks. The first workbook will be maintain by one or two people (myself included). I only want to update the first workbook when necessary and have it automatically update the second workbook. The second workbook will not make any changes to the first workbook. Is there a way to validate names w/o using the Data-Validation method or a combo box? I know this is a lot but I do appreciate any and all responses. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validating a name without using the Data Validation function
The short answer: It's not really clear what you're trying to do. Perhaps a
VLOOKUP or other lookup function would work. If you post formulas you're currently using, and provide some detail about when they work and when they don't, maybe someone could come up with a specific answer. The longer answer: you may need to create a real database, not emulate one in MS Excel. My experience is that you will run into data integrity issues with the scheme you have, and all your efforts will be for naught. Excel's not a database program, and expecting it to reliably retrieve data from external tables of data is a dicey proposition, made even more so when you state that multiple people will be maintaining the data. -- Brevity is the soul of wit. "twynsys" wrote: I am working in two workbooks. One workbook contains data that need to be populated into the second workbook. I want to be able to input a person's last name into the second workbook and have it automatically retrieve the data (first name and manager's first & last name) from the first workbook and populate itself with the data. I am able to do all that but I have a problem when it come to people that have the same last name or the same first and last name. It searches the first workbook and pulls the data for the first match, it will not search past the first match. The only way to differentiate between the people is by their manager's name (hoping they have different managers). There are key identifiers but the people using the second workbook would not know what they are, so they cannot use them. Normally the first part of the problem could be resolved by using the Data- Validation method. For the purpose of this project I cannot use the Data- Validation method. The Data-Validation requires the first workbook to open or be a second worksheet in the second workbook. I don't want that to happen because of maintenance and the second workbook will be used by an entire dept and can be used as a template for other workbooks. The first workbook will be maintain by one or two people (myself included). I only want to update the first workbook when necessary and have it automatically update the second workbook. The second workbook will not make any changes to the first workbook. Is there a way to validate names w/o using the Data-Validation method or a combo box? I know this is a lot but I do appreciate any and all responses. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validating a name without using the Data Validation function
I agree with Dave - a database is likely the better approach. Nick Hodge
explains how to use parameter queries to pull data from Access http://www.nickhodge.co.uk/gui/datam...taexamples.htm but the advice works equally well with SQL Server (or even an Excel worksheet set up as a database). I'd use Nick's advice in conjunction with a test for the number of rows that are returned for a given surname. If more than one row is returned, you can populate a list box on a popup form and let the user choose the correct data. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validating a name without using the Data Validation function
Thanks Dave & Duke,
Using Access was my initial decision but Access is not supported by the company's IT staff, so if something happens with the app and I cannot fix it then the dept is S-O-L. As far as using an SQL server...well that is a can of worms that I do not want to open. Break down of what I'm trying to do: Workbook A has a list of everyone's first name, alias, last name, manager, director, and employee id in a dept. Workbook B is used track information about the people in Workbook A. In Workbook B, I want to be able to type in a person's last name and have their first name (or alias) and manager name automatically appear. I can do that with the following codes: =IF(VLOOKUP(A:A,First_Name,3,0)=0,VLOOKUP(A:A,Firs t_Name,2,0),VLOOKUP(A:A,First_Name,3,0)) <-----This retrieves the inputted last name from Column A of Workbook B and lookup that value from a range named First Name that is in Workbook A. It looks to see if that last name has an associated Alias if so, it displays the Alias name in Column C of Workbook B, if it does not have an associated Alias it displays the First Name. =VLOOKUP(A:A&" "&B:B,Manager_Last_Name,4,0) <---This formula combines the values from Column A and Column B in Workbook B and then looks up that combined value in a range name Manager Last Name in Workbook A. It returns the value in Column C of Workbook B. =VLOOKUP(A:A&" "&B:B,Manager_First_Name,5,0) <-----same as the formula above but it returns the first name of the manager in Column D in Workbook B EX of Workbook A: Employee Last Name First Name Alias Full Name Manager Manager ID Last Name First Name 123456 James Rhonda Taylor Rhonda Fowler Robert 654321 Jones Patricia Patty Jones Patricia C. Fowler Robert 234561 Peters Lynne Peters Lynne Fowler Robert 345612 Peters Kenneth Ken Kenneth Ken Doe John 456123 Johnson Michael Johnson Michael Doe John 561234 Johnson Michael Johnson Michael Jackson Steve Ex of Workbook B: Column A Column B Column C Column D James Rhonda Fowler Robert Jones Patty Fowler Robert Peters Lynne Fowler Robert <---- This should be Ken not Lynne Johnson Michael Doe John <---- The mgr should be Steve not John The problem is with multiple people having the same name or last name. The first formula cannot determine if there is more than one of the same last name. It picks the first one and returns the value. The logical thing to is perform a search on the employee's ID but the person using Workbook B will not know the that ID#. They will only know the first and last name of the employee. "Dave F" wrote: The short answer: It's not really clear what you're trying to do. Perhaps a VLOOKUP or other lookup function would work. If you post formulas you're currently using, and provide some detail about when they work and when they don't, maybe someone could come up with a specific answer. The longer answer: you may need to create a real database, not emulate one in MS Excel. My experience is that you will run into data integrity issues with the scheme you have, and all your efforts will be for naught. Excel's not a database program, and expecting it to reliably retrieve data from external tables of data is a dicey proposition, made even more so when you state that multiple people will be maintaining the data. -- Brevity is the soul of wit. "twynsys" wrote: I am working in two workbooks. One workbook contains data that need to be populated into the second workbook. I want to be able to input a person's last name into the second workbook and have it automatically retrieve the data (first name and manager's first & last name) from the first workbook and populate itself with the data. I am able to do all that but I have a problem when it come to people that have the same last name or the same first and last name. It searches the first workbook and pulls the data for the first match, it will not search past the first match. The only way to differentiate between the people is by their manager's name (hoping they have different managers). There are key identifiers but the people using the second workbook would not know what they are, so they cannot use them. Normally the first part of the problem could be resolved by using the Data- Validation method. For the purpose of this project I cannot use the Data- Validation method. The Data-Validation requires the first workbook to open or be a second worksheet in the second workbook. I don't want that to happen because of maintenance and the second workbook will be used by an entire dept and can be used as a template for other workbooks. The first workbook will be maintain by one or two people (myself included). I only want to update the first workbook when necessary and have it automatically update the second workbook. The second workbook will not make any changes to the first workbook. Is there a way to validate names w/o using the Data-Validation method or a combo box? I know this is a lot but I do appreciate any and all responses. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data validation: formula for validating only multiples of 10 | Excel Worksheet Functions | |||
ranking query | Excel Discussion (Misc queries) | |||
Case Sensitivity problem with data validation | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Printing data validation scenarios | Excel Worksheet Functions |