Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,081
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
data validation: formula for validating only multiples of 10 Excelnovice Excel Worksheet Functions 3 May 15th 06 07:17 PM
ranking query JaimeTimbrell Excel Discussion (Misc queries) 2 February 16th 06 09:09 AM
Case Sensitivity problem with data validation Upya Excel Worksheet Functions 3 October 7th 05 01:30 AM
Help PLEASE! Not sure what answer is: Match? Index? Other? baz Excel Worksheet Functions 7 September 3rd 05 03:47 PM
Printing data validation scenarios SJC Excel Worksheet Functions 14 July 24th 05 12:43 AM


All times are GMT +1. The time now is 08:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"