Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
Hmmm....I started with a blank workbook...
€¢ Named a sheet: Data €¢ Named a sheet Feb_09 €¢ Followed the instructions from my prior post €¢ The dropdowns display the first value €¢ I entered a non-listed value €¢ The DV dropdowns showed the new item in the list Some other issue must be muddying the waters. Please let us know what you find. Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
Ron,
I decided to create a new blank workbook with just the two worksheets Feb_09 and DATA. I did this thinking that my other formulas and lists may be interfering with this one, but I still get the #N/A error. Is there an email address where I can send the workbook so that you may take a look at what I might be doing wrong? Mike "Ron Coderre" wrote: Hmmm....I started with a blank workbook... €¢ Named a sheet: Data €¢ Named a sheet Feb_09 €¢ Followed the instructions from my prior post €¢ The dropdowns display the first value €¢ I entered a non-listed value €¢ The DV dropdowns showed the new item in the list Some other issue must be muddying the waters. Please let us know what you find. Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... 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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
Try this:
http://freefilehosting.net/index.cfm or any other free file hosting site: http://cjoint.com/index.php http://www.savefile.com/index.php http://drop.io/ Then post the link so all interested parties can see what we're working with. Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... Ron, I decided to create a new blank workbook with just the two worksheets Feb_09 and DATA. I did this thinking that my other formulas and lists may be interfering with this one, but I still get the #N/A error. Is there an email address where I can send the workbook so that you may take a look at what I might be doing wrong? Mike "Ron Coderre" wrote: Hmmm....I started with a blank workbook... €¢ Named a sheet: Data €¢ Named a sheet Feb_09 €¢ Followed the instructions from my prior post €¢ The dropdowns display the first value €¢ I entered a non-listed value €¢ The DV dropdowns showed the new item in the list Some other issue must be muddying the waters. Please let us know what you find. Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... 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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
I'll have to try and post this from home tonight, our network administrator
has all file hosting sites blocked from our access. Thanks, Mike "Ron Coderre" wrote: Try this: http://freefilehosting.net/index.cfm or any other free file hosting site: http://cjoint.com/index.php http://www.savefile.com/index.php http://drop.io/ Then post the link so all interested parties can see what we're working with. Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... Ron, I decided to create a new blank workbook with just the two worksheets Feb_09 and DATA. I did this thinking that my other formulas and lists may be interfering with this one, but I still get the #N/A error. Is there an email address where I can send the workbook so that you may take a look at what I might be doing wrong? Mike "Ron Coderre" wrote: Hmmm....I started with a blank workbook... €¢ Named a sheet: Data €¢ Named a sheet Feb_09 €¢ Followed the instructions from my prior post €¢ The dropdowns display the first value €¢ I entered a non-listed value €¢ The DV dropdowns showed the new item in the list Some other issue must be muddying the waters. Please let us know what you find. Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... 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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
The workbook has been posted online at the following site:
http://freefilehosting.net/download/46a5c Thanks Ron. I hope someone can find my mistake. Mike "watermt" wrote: I'll have to try and post this from home tonight, our network administrator has all file hosting sites blocked from our access. Thanks, Mike "Ron Coderre" wrote: Try this: http://freefilehosting.net/index.cfm or any other free file hosting site: http://cjoint.com/index.php http://www.savefile.com/index.php http://drop.io/ Then post the link so all interested parties can see what we're working with. Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... Ron, I decided to create a new blank workbook with just the two worksheets Feb_09 and DATA. I did this thinking that my other formulas and lists may be interfering with this one, but I still get the #N/A error. Is there an email address where I can send the workbook so that you may take a look at what I might be doing wrong? Mike "Ron Coderre" wrote: Hmmm....I started with a blank workbook... €¢ Named a sheet: Data €¢ Named a sheet Feb_09 €¢ Followed the instructions from my prior post €¢ The dropdowns display the first value €¢ I entered a non-listed value €¢ The DV dropdowns showed the new item in the list Some other issue must be muddying the waters. Please let us know what you find. Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... 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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
The link opens a Word document with some commentary and the posted
instructions: 2609_DataValFlexList_Word document.doc. Where did you post the Excel workbook? Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... The workbook has been posted online at the following site: http://freefilehosting.net/download/46a5c Thanks Ron. I hope someone can find my mistake. Mike "watermt" wrote: I'll have to try and post this from home tonight, our network administrator has all file hosting sites blocked from our access. Thanks, Mike "Ron Coderre" wrote: Try this: http://freefilehosting.net/index.cfm or any other free file hosting site: http://cjoint.com/index.php http://www.savefile.com/index.php http://drop.io/ Then post the link so all interested parties can see what we're working with. Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... Ron, I decided to create a new blank workbook with just the two worksheets Feb_09 and DATA. I did this thinking that my other formulas and lists may be interfering with this one, but I still get the #N/A error. Is there an email address where I can send the workbook so that you may take a look at what I might be doing wrong? Mike "Ron Coderre" wrote: Hmmm....I started with a blank workbook... €¢ Named a sheet: Data €¢ Named a sheet Feb_09 €¢ Followed the instructions from my prior post €¢ The dropdowns display the first value €¢ I entered a non-listed value €¢ The DV dropdowns showed the new item in the list Some other issue must be muddying the waters. Please let us know what you find. Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... 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 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
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 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
Ron
The workbook was sent to me. Only problem I could find was the formulas in Data!F4:F43 were not array-entered with CSE. Hence the #N/A error. Sent a revised working edition back to Mike. Gord On Fri, 27 Mar 2009 07:31:49 -0400, "Ron Coderre" wrote: The link opens a Word document with some commentary and the posted instructions: 2609_DataValFlexList_Word document.doc. Where did you post the Excel workbook? Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... The workbook has been posted online at the following site: http://freefilehosting.net/download/46a5c Thanks Ron. I hope someone can find my mistake. Mike "watermt" wrote: I'll have to try and post this from home tonight, our network administrator has all file hosting sites blocked from our access. Thanks, Mike "Ron Coderre" wrote: Try this: http://freefilehosting.net/index.cfm or any other free file hosting site: http://cjoint.com/index.php http://www.savefile.com/index.php http://drop.io/ Then post the link so all interested parties can see what we're working with. Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... Ron, I decided to create a new blank workbook with just the two worksheets Feb_09 and DATA. I did this thinking that my other formulas and lists may be interfering with this one, but I still get the #N/A error. Is there an email address where I can send the workbook so that you may take a look at what I might be doing wrong? Mike "Ron Coderre" wrote: Hmmm....I started with a blank workbook... • Named a sheet: Data • Named a sheet Feb_09 • Followed the instructions from my prior post • The dropdowns display the first value • I entered a non-listed value • The DV dropdowns showed the new item in the list Some other issue must be muddying the waters. Please let us know what you find. Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... 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 |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
Thanks much, Gord...That's what I suspected.
Best Regards, Ron Coderre Microsoft MVP (Excel) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Ron The workbook was sent to me. Only problem I could find was the formulas in Data!F4:F43 were not array-entered with CSE. Hence the #N/A error. Sent a revised working edition back to Mike. Gord On Fri, 27 Mar 2009 07:31:49 -0400, "Ron Coderre" wrote: The link opens a Word document with some commentary and the posted instructions: 2609_DataValFlexList_Word document.doc. Where did you post the Excel workbook? Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... The workbook has been posted online at the following site: http://freefilehosting.net/download/46a5c Thanks Ron. I hope someone can find my mistake. Mike "watermt" wrote: I'll have to try and post this from home tonight, our network administrator has all file hosting sites blocked from our access. Thanks, Mike "Ron Coderre" wrote: Try this: http://freefilehosting.net/index.cfm or any other free file hosting site: http://cjoint.com/index.php http://www.savefile.com/index.php http://drop.io/ Then post the link so all interested parties can see what we're working with. Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... Ron, I decided to create a new blank workbook with just the two worksheets Feb_09 and DATA. I did this thinking that my other formulas and lists may be interfering with this one, but I still get the #N/A error. Is there an email address where I can send the workbook so that you may take a look at what I might be doing wrong? Mike "Ron Coderre" wrote: Hmmm....I started with a blank workbook... . Named a sheet: Data . Named a sheet Feb_09 . Followed the instructions from my prior post . The dropdowns display the first value . I entered a non-listed value . The DV dropdowns showed the new item in the list Some other issue must be muddying the waters. Please let us know what you find. Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... 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 |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
Ron,
I finally got this figured out with the assistance of you and Gord. I really appreciate your time Ron (and Gords). Apparently I was not enabling the CTRL+SHIFT+ENTER command at the "right time". I was doing it before typing in the code instead of after I had already typed the code. You guys are fantastic and I am so glad that I found the Discussion Forum. It's certainly educating me, and opening up a great number of more uses for Excel in my daily work. Besides, I get to communicate with a lot of great people! Thanks fellas, Mike "Ron Coderre" wrote: Hmmm....I started with a blank workbook... €¢ Named a sheet: Data €¢ Named a sheet Feb_09 €¢ Followed the instructions from my prior post €¢ The dropdowns display the first value €¢ I entered a non-listed value €¢ The DV dropdowns showed the new item in the list Some other issue must be muddying the waters. Please let us know what you find. Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... 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 |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List - adding new entries
You're very welcome. Glad we could help!
Regards, Ron Coderre Microsoft MVP (Excel) "watermt" wrote in message ... Ron, I finally got this figured out with the assistance of you and Gord. I really appreciate your time Ron (and Gords). Apparently I was not enabling the CTRL+SHIFT+ENTER command at the "right time". I was doing it before typing in the code instead of after I had already typed the code. You guys are fantastic and I am so glad that I found the Discussion Forum. It's certainly educating me, and opening up a great number of more uses for Excel in my daily work. Besides, I get to communicate with a lot of great people! Thanks fellas, Mike "Ron Coderre" wrote: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation cell entries (Drop-down list) | Excel Discussion (Misc queries) | |||
View all entries in Validation List | Excel Discussion (Misc queries) | |||
blank entries in data validation list | Excel Worksheet Functions | |||
DataValidationList - Unique Entries | New Users to Excel | |||
update data validation list with new entries?? | Excel Discussion (Misc queries) |