I have a Validation list (=PhysiciansLN) that lists the last names of
physicians in our hospital. If a user of this Excel file does not find the
physicians last name in the =PhysiciansLN list is there a way that I can
allow the user to type the new last name and have that automatically added to
the =PhysiciansLN list?

I was hoping to be able to add new last names to that list through a formula
or function?

Mike

There's an example of a flexible data validation list at this website:
http://www.contextures.com/excelfilesRon.html

See this file: RDV0001 - Flexible Item List

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)

"watermt" wrote in message
...
I have a Validation list (=PhysiciansLN) that lists the last names of
physicians in our hospital. If a user of this Excel file does not find
the
physicians last name in the =PhysiciansLN list is there a way that I can
allow the user to type the new last name and have that automatically added
to
the =PhysiciansLN list?

I was hoping to be able to add new last names to that list through a
formula
or function?

Mike

I was just able to take a quick look at the web page. I think it may do the
trick (if I can figure out how to get it to work bewtween two worksheets). I
have a project meeting starting soon, will try later and get back to you.

Thanks,
Mike

"Ron Coderre" wrote:

There's an example of a flexible data validation list at this website:
http://www.contextures.com/excelfilesRon.html

See this file: RDV0001 - Flexible Item List

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)

"watermt" wrote in message
...
I have a Validation list (=PhysiciansLN) that lists the last names of
physicians in our hospital. If a user of this Excel file does not find
the
physicians last name in the =PhysiciansLN list is there a way that I can
allow the user to type the new last name and have that automatically added
to
the =PhysiciansLN list?

I was hoping to be able to add new last names to that list through a
formula
or function?

Mike

Go to the second sheet and select the entire column(or enough of that column
so you will never run out of rows) and name it, say MyList.

Now on the first sheet in the Data Valadation check List and in the Source
box enter =MyList OK.

HTH
Regards,
Howard

"watermt" wrote in message
...
I have a Validation list (=PhysiciansLN) that lists the last names of
physicians in our hospital. If a user of this Excel file does not find
the
physicians last name in the =PhysiciansLN list is there a way that I can
allow the user to type the new last name and have that automatically added
to
the =PhysiciansLN list?

I was hoping to be able to add new last names to that list through a
formula
or function?

Mike

Howard

That won't add new items to the list.

Debra Dalgleish has a couple of methods in these download sample workbooks.

http://www.contextures.on.ca/excelfiles.html#DV0038

http://www.contextures.on.ca/excelfiles.html#DV0012


Gord Dibben MS Excel MVP

On Thu, 19 Mar 2009 15:32:10 -0700, "L. Howard Kittle"
wrote:

Go to the second sheet and select the entire column(or enough of that column
so you will never run out of rows) and name it, say MyList.

Now on the first sheet in the Data Valadation check List and in the Source
box enter =MyList OK.

HTH
Regards,
Howard

"watermt" wrote in message
...
I have a Validation list (=PhysiciansLN) that lists the last names of
physicians in our hospital. If a user of this Excel file does not find
the
physicians last name in the =PhysiciansLN list is there a way that I can
allow the user to type the new last name and have that automatically added
to
the =PhysiciansLN list?

I was hoping to be able to add new last names to that list through a
formula
or function?

Mike

Thanks to both Howard and Gord, I am out of the office today but will
hopefully be in a position to try your recommendations. I will let you know
and this discussion group know if one or more suggestions resolved my issue.

Mike

"Gord Dibben" wrote:

Howard

That won't add new items to the list.

Debra Dalgleish has a couple of methods in these download sample workbooks.

http://www.contextures.on.ca/excelfiles.html#DV0038

http://www.contextures.on.ca/excelfiles.html#DV0012


Gord Dibben MS Excel MVP

On Thu, 19 Mar 2009 15:32:10 -0700, "L. Howard Kittle"
wrote:

Go to the second sheet and select the entire column(or enough of that column
so you will never run out of rows) and name it, say MyList.

Now on the first sheet in the Data Valadation check List and in the Source
box enter =MyList OK.

HTH
Regards,
Howard

"watermt" wrote in message
...
I have a Validation list (=PhysiciansLN) that lists the last names of
physicians in our hospital. If a user of this Excel file does not find
the
physicians last name in the =PhysiciansLN list is there a way that I can
allow the user to type the new last name and have that automatically added
to
the =PhysiciansLN list?

I was hoping to be able to add new last names to that list through a
formula
or function?

Mike

Gord,
I've tried to get the DataValFlexList.xls sample to work for me but have had
no luck. In the sample it refers to column B as List Items B2:B20; my list
items (named Docs) to update is on a seperate worksheet named DATA.

Also in the sample it refers to column D as the User Input D2:D20; my user
input (named Referred BY) to select and enter in on the worksheet named
Feb_09.

My range definitions are as follows:

Docs:
=DATA!$F$3:INDEX(DATA!$F:$F,COUNTIF(DATA!$F:$F,"?* ")+COUNT(DATA!$F:$F),1)

Referred BY: =Feb_09!$H$10:$H$43

From what I've offered here, can you tell me where I've made an error or
where I've overlooked something in one of the formulas?

Much appreciated,
Mike


"Gord Dibben" wrote:

Howard

That won't add new items to the list.

Debra Dalgleish has a couple of methods in these download sample workbooks.

http://www.contextures.on.ca/excelfiles.html#DV0038

http://www.contextures.on.ca/excelfiles.html#DV0012


Gord Dibben MS Excel MVP

On Thu, 19 Mar 2009 15:32:10 -0700, "L. Howard Kittle"
wrote:

Go to the second sheet and select the entire column(or enough of that column
so you will never run out of rows) and name it, say MyList.

Now on the first sheet in the Data Valadation check List and in the Source
box enter =MyList OK.

HTH
Regards,
Howard

"watermt" wrote in message
...
I have a Validation list (=PhysiciansLN) that lists the last names of
physicians in our hospital. If a user of this Excel file does not find
the
physicians last name in the =PhysiciansLN list is there a way that I can
allow the user to type the new last name and have that automatically added
to
the =PhysiciansLN list?

I was hoping to be able to add new last names to that list through a
formula
or function?

Mike

I'm confused.........easily done<g

Can you send me the workbook to my email?

Change the AT and DOT in my posted email address.


Gord

On Tue, 24 Mar 2009 13:33:00 -0700, watermt
wrote:

Gord,
I've tried to get the DataValFlexList.xls sample to work for me but have had
no luck. In the sample it refers to column B as List Items B2:B20; my list
items (named Docs) to update is on a seperate worksheet named DATA.

Also in the sample it refers to column D as the User Input D2:D20; my user
input (named Referred BY) to select and enter in on the worksheet named
Feb_09.

My range definitions are as follows:

Docs:
=DATA!$F$3:INDEX(DATA!$F:$F,COUNTIF(DATA!$F:$F,"? *")+COUNT(DATA!$F:$F),1)

Referred BY: =Feb_09!$H$10:$H$43

From what I've offered here, can you tell me where I've made an error or
where I've overlooked something in one of the formulas?

Much appreciated,
Mike


"Gord Dibben" wrote:

Howard

That won't add new items to the list.

Debra Dalgleish has a couple of methods in these download sample workbooks.

http://www.contextures.on.ca/excelfiles.html#DV0038

http://www.contextures.on.ca/excelfiles.html#DV0012


Gord Dibben MS Excel MVP

On Thu, 19 Mar 2009 15:32:10 -0700, "L. Howard Kittle"
wrote:

Go to the second sheet and select the entire column(or enough of that column
so you will never run out of rows) and name it, say MyList.

Now on the first sheet in the Data Valadation check List and in the Source
box enter =MyList OK.

HTH
Regards,
Howard

"watermt" wrote in message
...
I have a Validation list (=PhysiciansLN) that lists the last names of
physicians in our hospital. If a user of this Excel file does not find
the
physicians last name in the =PhysiciansLN list is there a way that I can
allow the user to type the new last name and have that automatically added
to
the =PhysiciansLN list?

I was hoping to be able to add new last names to that list through a
formula
or function?

Mike

I tried to post the workbokk online last night but had two files in the
workbbok. One for the instructions and the other the actual workbook. I
posted the instructions instead by mistake.

Ron Coderre was working on this with me too. I don't have access to re-post
here at work, so I will email the workbook to you as requested.

Thanks to you and Ron for being so patient in dealing with my shortcomings,
Mike

"Gord Dibben" wrote:

I'm confused.........easily done<g

Can you send me the workbook to my email?

Change the AT and DOT in my posted email address.


Gord

On Tue, 24 Mar 2009 13:33:00 -0700, watermt
wrote:

Gord,
I've tried to get the DataValFlexList.xls sample to work for me but have had
no luck. In the sample it refers to column B as List Items B2:B20; my list
items (named Docs) to update is on a seperate worksheet named DATA.

Also in the sample it refers to column D as the User Input D2:D20; my user
input (named Referred BY) to select and enter in on the worksheet named
Feb_09.

My range definitions are as follows:

Docs:
=DATA!$F$3:INDEX(DATA!$F:$F,COUNTIF(DATA!$F:$F,"? *")+COUNT(DATA!$F:$F),1)

Referred BY: =Feb_09!$H$10:$H$43

From what I've offered here, can you tell me where I've made an error or
where I've overlooked something in one of the formulas?

Much appreciated,
Mike


"Gord Dibben" wrote:

Howard

That won't add new items to the list.

Debra Dalgleish has a couple of methods in these download sample workbooks.

http://www.contextures.on.ca/excelfiles.html#DV0038

http://www.contextures.on.ca/excelfiles.html#DV0012


Gord Dibben MS Excel MVP

On Thu, 19 Mar 2009 15:32:10 -0700, "L. Howard Kittle"
wrote:

Go to the second sheet and select the entire column(or enough of that column
so you will never run out of rows) and name it, say MyList.

Now on the first sheet in the Data Valadation check List and in the Source
box enter =MyList OK.

HTH
Regards,
Howard

"watermt" wrote in message
...
I have a Validation list (=PhysiciansLN) that lists the last names of
physicians in our hospital. If a user of this Excel file does not find
the
physicians last name in the =PhysiciansLN list is there a way that I can
allow the user to type the new last name and have that automatically added
to
the =PhysiciansLN list?

I was hoping to be able to add new last names to that list through a
formula
or function?

Mike

Try this on your workbook:

Names in Workbook: Docs
Refers to:
=Data!$F$3:INDEX(Data!$F:$F,COUNTIF(Data!$F:$F,"?* ")+COUNT(Data!$F:$F)+2,1)

Names in Workbook: ReferredBy
Refers to: =Feb_09!$H$10:$H$43

Select the ReferredBy range (Feb_09!$H$10:$H$43)
Data.Validation
....Allow: list
....Source: Docs

On the Data sheet
F3: (the first value you want on the list)

The first ARRAY FORMULA (committed with CTRL+SHIFT+ENTER):
F4: =IF(SUMPRODUCT((ReferredBy<"")*
ISERROR(MATCH(ReferredBy,$F$3:F3,0)))<0,
INDEX(ReferredBy,MATCH(TRUE,ISERROR(IF(ISBLANK(Ref erredBy),
FALSE,MATCH(ReferredBy,$F$3:$F3,0))),0),1),"")

Copy that formula into F5 and down as far as you think you'll need.

Now test the input range.

That should work...Does it?

Regards,

Ron Coderre
Microsoft MVP (Excel)


"watermt" wrote in message
...
Gord,
I've tried to get the DataValFlexList.xls sample to work for me but have
had
no luck. In the sample it refers to column B as List Items B2:B20; my
list
items (named Docs) to update is on a seperate worksheet named DATA.

Also in the sample it refers to column D as the User Input D2:D20; my user
input (named Referred BY) to select and enter in on the worksheet named
Feb_09.

My range definitions are as follows:

Docs:
=DATA!$F$3:INDEX(DATA!$F:$F,COUNTIF(DATA!$F:$F,"?* ")+COUNT(DATA!$F:$F),1)

Referred BY: =Feb_09!$H$10:$H$43

From what I've offered here, can you tell me where I've made an error or
where I've overlooked something in one of the formulas?

Much appreciated,
Mike


"Gord Dibben" wrote:

Howard

That won't add new items to the list.

Debra Dalgleish has a couple of methods in these download sample
workbooks.

http://www.contextures.on.ca/excelfiles.html#DV0038

http://www.contextures.on.ca/excelfiles.html#DV0012


Gord Dibben MS Excel MVP

On Thu, 19 Mar 2009 15:32:10 -0700, "L. Howard Kittle"
wrote:

Go to the second sheet and select the entire column(or enough of that
column
so you will never run out of rows) and name it, say MyList.

Now on the first sheet in the Data Valadation check List and in the
Source
box enter =MyList OK.

HTH
Regards,
Howard

"watermt" wrote in message
...
I have a Validation list (=PhysiciansLN) that lists the last names of
physicians in our hospital. If a user of this Excel file does not
find
the
physicians last name in the =PhysiciansLN list is there a way that I
can
allow the user to type the new last name and have that automatically
added
to
the =PhysiciansLN list?

I was hoping to be able to add new last names to that list through a
formula
or function?

Mike

Ron,
I follow your directions to the letter (I think!) but now when I enter a new
Doc name the #N/A error message shows up in the list instead of the new Docs
name.

I went to the help feature and they suggested since I am using an array
formula with the MATCH function that I make my rows equal for the Docs list
and the RefferedBy list, which I did. Still getting a #N/A error.

Mike

"Ron Coderre" wrote:

Try this on your workbook:

Names in Workbook: Docs
Refers to:
=Data!$F$3:INDEX(Data!$F:$F,COUNTIF(Data!$F:$F,"?* ")+COUNT(Data!$F:$F)+2,1)

Names in Workbook: ReferredBy
Refers to: =Feb_09!$H$10:$H$43

Select the ReferredBy range (Feb_09!$H$10:$H$43)
Data.Validation
....Allow: list
....Source: Docs

On the Data sheet
F3: (the first value you want on the list)

The first ARRAY FORMULA (committed with CTRL+SHIFT+ENTER):
F4: =IF(SUMPRODUCT((ReferredBy<"")*
ISERROR(MATCH(ReferredBy,$F$3:F3,0)))<0,
INDEX(ReferredBy,MATCH(TRUE,ISERROR(IF(ISBLANK(Ref erredBy),
FALSE,MATCH(ReferredBy,$F$3:$F3,0))),0),1),"")

Copy that formula into F5 and down as far as you think you'll need.

Now test the input range.

That should work...Does it?

Regards,

Ron Coderre
Microsoft MVP (Excel)


"watermt" wrote in message
...
Gord,
I've tried to get the DataValFlexList.xls sample to work for me but have
had
no luck. In the sample it refers to column B as List Items B2:B20; my
list
items (named Docs) to update is on a seperate worksheet named DATA.

Also in the sample it refers to column D as the User Input D2:D20; my user
input (named Referred BY) to select and enter in on the worksheet named
Feb_09.

My range definitions are as follows:

Docs:
=DATA!$F$3:INDEX(DATA!$F:$F,COUNTIF(DATA!$F:$F,"?* ")+COUNT(DATA!$F:$F),1)

Referred BY: =Feb_09!$H$10:$H$43

From what I've offered here, can you tell me where I've made an error or
where I've overlooked something in one of the formulas?

Much appreciated,
Mike


"Gord Dibben" wrote:

Howard

That won't add new items to the list.

Debra Dalgleish has a couple of methods in these download sample
workbooks.

http://www.contextures.on.ca/excelfiles.html#DV0038

http://www.contextures.on.ca/excelfiles.html#DV0012


Gord Dibben MS Excel MVP

On Thu, 19 Mar 2009 15:32:10 -0700, "L. Howard Kittle"
wrote:

Go to the second sheet and select the entire column(or enough of that
column
so you will never run out of rows) and name it, say MyList.

Now on the first sheet in the Data Valadation check List and in the
Source
box enter =MyList OK.

HTH
Regards,
Howard

"watermt" wrote in message
...
I have a Validation list (=PhysiciansLN) that lists the last names of
physicians in our hospital. If a user of this Excel file does not
find
the
physicians last name in the =PhysiciansLN list is there a way that I
can
allow the user to type the new last name and have that automatically
added
to
the =PhysiciansLN list?

I was hoping to be able to add new last names to that list through a
formula
or function?

Mike