I have two cells I fill out 20-40 times a day, NAME and TITLE, and I
have a drop down box in which I have the titles. Is there a way I can
make it so that my sheet "remembers" a NAME and auto fills the title,
and, updates itself both name and title if it doesn't recognize the
name?
So next time I happen to type that name the title fills. I am sort of
proficient with excel, so fire away.

Thanx for any input....

Ryk

On 3 Sep 2006, "Ryk" wrote:

I have two cells I fill out 20-40 times a day, NAME and TITLE, and I
have a drop down box in which I have the titles. Is there a way I can
make it so that my sheet "remembers" a NAME and auto fills the title,
and, updates itself both name and title if it doesn't recognize the
name?
So next time I happen to type that name the title fills. I am sort of
proficient with excel, so fire away.

Thanx for any input....

Ryk

Ryk,

You could use a VLOOKUP to match the name you input to its
corresponding title in a table.

Let's assume you want to put the name in column A and have the title
populate in column B on one worksheet, and that you have a row with
the headers "Name" and "Title" in row 1. On a second worksheet,
create a table with the names in column A and the titles in column B.

The formula in column B of your first worksheet would look similar to
this:

=IF(A2="","",VLOOKUP(A2, Sheet2!A:B,2,FALSE))

By putting that in column B, you just have to enter the name into
column A and the cell next to it will look for the name in your table
on Sheet2 and return the title.

In the event that the person does not exist in the table on Sheet2,
this formula will return an error. You could easily clean this up by
using this formula instead:

=IF(A2="","",IF(ISNA(VLOOKUP(A2,Sheet2!A:B,2,FALSE )),"Not
Found",VLOOKUP(A2,Sheet2!A:B,2,FALSE)))

Now you would get the text "Not Found" if the person isn't in the
table, rather than the standard "#N/A" error.

MP-

Thanks for reply Mangus,

You explained the VLOOKUP and thats all fine, but what I want is when
it doesn't recognize a name, it adds it to the VLOOKUP list, and then
maybe has a popup for title too add?

Ryk

Hmmm maybe I'd better explain Mangus... the names and titles could
amount to 7000-8000 in total, and adding by hand is not something I'd
like to do, so this way it would eventually work on its own, or let me
know users isn't in list, and I'd be able to add them.

Ryk

On 3 Sep 2006, "Ryk" wrote:

Hmmm maybe I'd better explain Mangus... the names and titles could
amount to 7000-8000 in total, and adding by hand is not something I'd
like to do, so this way it would eventually work on its own, or let me
know users isn't in list, and I'd be able to add them.

You could write a small VB form to run from a macro that would let you
input a name and title, and then add it to the bottom of your list..

But if you're going to have to type the name and title anyway, is that
so different from typing it into the table?

MP-

Was just trying to have the table hidden, out of sight out of mind.

Ryk

Mangus Pyke wrote:
On 3 Sep 2006, "Ryk" wrote:

Hmmm maybe I'd better explain Mangus... the names and titles could
amount to 7000-8000 in total, and adding by hand is not something I'd
like to do, so this way it would eventually work on its own, or let me
know users isn't in list, and I'd be able to add them.

You could write a small VB form to run from a macro that would let you
input a name and title, and then add it to the bottom of your list..

But if you're going to have to type the name and title anyway, is that
so different from typing it into the table?

MP-

And I have done alot in excel, but don't understand the VB forms as of
yet, but heck beginning of last week I had never done a macro either,
have done ok with those. I'll look more into the VB forms, any good
areas to find info? Shift about done, i'll check it tomorrow, thanks
Mangus.

Ryk

On 3 Sep 2006, "Ryk" wrote:
And I have done alot in excel, but don't understand the VB forms as of
yet, but heck beginning of last week I had never done a macro either,
have done ok with those. I'll look more into the VB forms, any good
areas to find info? Shift about done, i'll check it tomorrow, thanks
Mangus.

Ryk,

Try he
http://pubs.logicalexpressions.com/p...cle.asp?ID=362

There are a number of websites that have tutorials on creating forms
in VBA and it seems that you could hide the worksheet that contains
the table, so all you would have is the worksheet with your input data
displayed.

My suggestions:

1. Put the name in the name column and run a macro to populate the
title in the title column.

2. Write the macro to loop through the names in the name column and
look for a match.

3. If it finds a match, populate the title for you.. otherwise, popup
a form that has name and title fields, with the name already
populated.

4. Once you put in a title, have it add the name and title to the
lookup table for you and then repeat the lookup so that your title is
added to the input sheet.

This should take 10-15 minutes to put together for someone who already
knows how to do this. I don't mind doing it so that you'll have
something to learn from, provided that you actually get into it and
screw around with it a little so that you learn it.

Email me at manguspyke [at] comcast [dot] net and tell me where to
send the example.

MP-