Pb with Data validation based on named list

Hello,

I create a Name as Test = {"A" , "B"} (not a range but a list of datas)
by the menu 'Insert" / "Name" / "define"

Then i cant'use that name "Test" in the Data validation Combo box
by the menu "Datas" / "Validation" / "Option" / "Autorize" (List)
The error message is that system does'nt accept in "Source" = Test

Where is the Probleme

thanks for your help

Gilles PROVOST
0
GillesPFR (10)
2/4/2008 3:39:02 PM
excel 39879 articles. 2 followers. Follow

6 Replies
297 Views

Similar Articles

[PageSpeed] 46

Hi Gilles

You don't need the named range.
In Data Validation>List>Source>A,B

No need for the = sign before your list of values, just a comma (or ; if 
that is your Regional separator) between each item you wish to allow.

-- 
Regards
Roger Govier

"Gilles P (FR)" <GillesPFR@discussions.microsoft.com> wrote in message 
news:13BCABFB-E83F-4579-B24D-2E1152C46FDF@microsoft.com...
> Hello,
>
> I create a Name as Test = {"A" , "B"} (not a range but a list of datas)
> by the menu 'Insert" / "Name" / "define"
>
> Then i cant'use that name "Test" in the Data validation Combo box
> by the menu "Datas" / "Validation" / "Option" / "Autorize" (List)
> The error message is that system does'nt accept in "Source" = Test
>
> Where is the Probleme
>
> thanks for your help
>
> Gilles PROVOST 

0
Roger
2/4/2008 3:58:28 PM
Thanks for this way of solution but,...
I need to have an defined name for the drop-down list items because i re-use 
this name in a MATCH() function in others range of the worksheet.

Regards,
Gilles

"Roger Govier" wrote:

> Hi Gilles
> 
> You don't need the named range.
> In Data Validation>List>Source>A,B
> 
> No need for the = sign before your list of values, just a comma (or ; if 
> that is your Regional separator) between each item you wish to allow.
> 
> -- 
> Regards
> Roger Govier
> 
> "Gilles P (FR)" <GillesPFR@discussions.microsoft.com> wrote in message 
> news:13BCABFB-E83F-4579-B24D-2E1152C46FDF@microsoft.com...
> > Hello,
> >
> > I create a Name as Test = {"A" , "B"} (not a range but a list of datas)
> > by the menu 'Insert" / "Name" / "define"
> >
> > Then i cant'use that name "Test" in the Data validation Combo box
> > by the menu "Datas" / "Validation" / "Option" / "Autorize" (List)
> > The error message is that system does'nt accept in "Source" = Test
> >
> > Where is the Probleme
> >
> > thanks for your help
> >
> > Gilles PROVOST 
> 
0
GillesPFR (10)
2/4/2008 6:07:01 PM
Hi Gilles
Then try placing the values you want in cells A1:A2 of Sheet2and name Test 
as Sheet1!A1:A2

-- 
Regards
Roger Govier

"Gilles P (FR)" <GillesPFR@discussions.microsoft.com> wrote in message 
news:5E0D017F-34F4-4747-A0B3-197EAA6A30C6@microsoft.com...
> Thanks for this way of solution but,...
> I need to have an defined name for the drop-down list items because i 
> re-use
> this name in a MATCH() function in others range of the worksheet.
>
> Regards,
> Gilles
>
> "Roger Govier" wrote:
>
>> Hi Gilles
>>
>> You don't need the named range.
>> In Data Validation>List>Source>A,B
>>
>> No need for the = sign before your list of values, just a comma (or ; if
>> that is your Regional separator) between each item you wish to allow.
>>
>> -- 
>> Regards
>> Roger Govier
>>
>> "Gilles P (FR)" <GillesPFR@discussions.microsoft.com> wrote in message
>> news:13BCABFB-E83F-4579-B24D-2E1152C46FDF@microsoft.com...
>> > Hello,
>> >
>> > I create a Name as Test = {"A" , "B"} (not a range but a list of datas)
>> > by the menu 'Insert" / "Name" / "define"
>> >
>> > Then i cant'use that name "Test" in the Data validation Combo box
>> > by the menu "Datas" / "Validation" / "Option" / "Autorize" (List)
>> > The error message is that system does'nt accept in "Source" = Test
>> >
>> > Where is the Probleme
>> >
>> > thanks for your help
>> >
>> > Gilles PROVOST
>> 
0
Roger
2/4/2008 10:44:54 PM
Apologies, that should have read

Then try placing the values you want in cells A1:A2 of Sheet2 and name Test
as =Sheet2!A1:A2


-- 
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
news:4AA86500-0AA8-4661-A19B-E9AAE67EFE12@microsoft.com...
> Hi Gilles
> Then try placing the values you want in cells A1:A2 of Sheet2and name Test 
> as Sheet1!A1:A2
>
> -- 
> Regards
> Roger Govier
>
> "Gilles P (FR)" <GillesPFR@discussions.microsoft.com> wrote in message 
> news:5E0D017F-34F4-4747-A0B3-197EAA6A30C6@microsoft.com...
>> Thanks for this way of solution but,...
>> I need to have an defined name for the drop-down list items because i 
>> re-use
>> this name in a MATCH() function in others range of the worksheet.
>>
>> Regards,
>> Gilles
>>
>> "Roger Govier" wrote:
>>
>>> Hi Gilles
>>>
>>> You don't need the named range.
>>> In Data Validation>List>Source>A,B
>>>
>>> No need for the = sign before your list of values, just a comma (or ; if
>>> that is your Regional separator) between each item you wish to allow.
>>>
>>> -- 
>>> Regards
>>> Roger Govier
>>>
>>> "Gilles P (FR)" <GillesPFR@discussions.microsoft.com> wrote in message
>>> news:13BCABFB-E83F-4579-B24D-2E1152C46FDF@microsoft.com...
>>> > Hello,
>>> >
>>> > I create a Name as Test = {"A" , "B"} (not a range but a list of 
>>> > datas)
>>> > by the menu 'Insert" / "Name" / "define"
>>> >
>>> > Then i cant'use that name "Test" in the Data validation Combo box
>>> > by the menu "Datas" / "Validation" / "Option" / "Autorize" (List)
>>> > The error message is that system does'nt accept in "Source" = Test
>>> >
>>> > Where is the Probleme
>>> >
>>> > thanks for your help
>>> >
>>> > Gilles PROVOST
>>> 
0
Roger
2/5/2008 7:44:22 AM
Hey,
Really sorry to insist on it,

By my target is to avoid a new sheet only for store two datas....

However Thanks for your answers

Gilles



"Roger Govier" wrote:

> Apologies, that should have read
> 
> Then try placing the values you want in cells A1:A2 of Sheet2 and name Test
> as =Sheet2!A1:A2
> 
> 
> -- 
> Regards
> Roger Govier
> 
> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
> news:4AA86500-0AA8-4661-A19B-E9AAE67EFE12@microsoft.com...
> > Hi Gilles
> > Then try placing the values you want in cells A1:A2 of Sheet2and name Test 
> > as Sheet1!A1:A2
> >
> > -- 
> > Regards
> > Roger Govier
> >
> > "Gilles P (FR)" <GillesPFR@discussions.microsoft.com> wrote in message 
> > news:5E0D017F-34F4-4747-A0B3-197EAA6A30C6@microsoft.com...
> >> Thanks for this way of solution but,...
> >> I need to have an defined name for the drop-down list items because i 
> >> re-use
> >> this name in a MATCH() function in others range of the worksheet.
> >>
> >> Regards,
> >> Gilles
> >>
> >> "Roger Govier" wrote:
> >>
> >>> Hi Gilles
> >>>
> >>> You don't need the named range.
> >>> In Data Validation>List>Source>A,B
> >>>
> >>> No need for the = sign before your list of values, just a comma (or ; if
> >>> that is your Regional separator) between each item you wish to allow.
> >>>
> >>> -- 
> >>> Regards
> >>> Roger Govier
> >>>
> >>> "Gilles P (FR)" <GillesPFR@discussions.microsoft.com> wrote in message
> >>> news:13BCABFB-E83F-4579-B24D-2E1152C46FDF@microsoft.com...
> >>> > Hello,
> >>> >
> >>> > I create a Name as Test = {"A" , "B"} (not a range but a list of 
> >>> > datas)
> >>> > by the menu 'Insert" / "Name" / "define"
> >>> >
> >>> > Then i cant'use that name "Test" in the Data validation Combo box
> >>> > by the menu "Datas" / "Validation" / "Option" / "Autorize" (List)
> >>> > The error message is that system does'nt accept in "Source" = Test
> >>> >
> >>> > Where is the Probleme
> >>> >
> >>> > thanks for your help
> >>> >
> >>> > Gilles PROVOST
> >>> 
0
GillesPFR (10)
2/5/2008 4:05:04 PM
Gilles

You don't have to use another sheet.
Use any spare cells on your existing sheet.

There is no problem in using another sheet, however, it can be hidden if you 
wish.

-- 
Regards
Roger Govier

"Gilles P (FR)" <GillesPFR@discussions.microsoft.com> wrote in message 
news:3527CB24-A16E-4B63-B7A0-75466184CB86@microsoft.com...
> Hey,
> Really sorry to insist on it,
>
> By my target is to avoid a new sheet only for store two datas....
>
> However Thanks for your answers
>
> Gilles
>
>
>
> "Roger Govier" wrote:
>
>> Apologies, that should have read
>>
>> Then try placing the values you want in cells A1:A2 of Sheet2 and name 
>> Test
>> as =Sheet2!A1:A2
>>
>>
>> -- 
>> Regards
>> Roger Govier
>>
>> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message
>> news:4AA86500-0AA8-4661-A19B-E9AAE67EFE12@microsoft.com...
>> > Hi Gilles
>> > Then try placing the values you want in cells A1:A2 of Sheet2and name 
>> > Test
>> > as Sheet1!A1:A2
>> >
>> > -- 
>> > Regards
>> > Roger Govier
>> >
>> > "Gilles P (FR)" <GillesPFR@discussions.microsoft.com> wrote in message
>> > news:5E0D017F-34F4-4747-A0B3-197EAA6A30C6@microsoft.com...
>> >> Thanks for this way of solution but,...
>> >> I need to have an defined name for the drop-down list items because i
>> >> re-use
>> >> this name in a MATCH() function in others range of the worksheet.
>> >>
>> >> Regards,
>> >> Gilles
>> >>
>> >> "Roger Govier" wrote:
>> >>
>> >>> Hi Gilles
>> >>>
>> >>> You don't need the named range.
>> >>> In Data Validation>List>Source>A,B
>> >>>
>> >>> No need for the = sign before your list of values, just a comma (or ; 
>> >>> if
>> >>> that is your Regional separator) between each item you wish to allow.
>> >>>
>> >>> -- 
>> >>> Regards
>> >>> Roger Govier
>> >>>
>> >>> "Gilles P (FR)" <GillesPFR@discussions.microsoft.com> wrote in 
>> >>> message
>> >>> news:13BCABFB-E83F-4579-B24D-2E1152C46FDF@microsoft.com...
>> >>> > Hello,
>> >>> >
>> >>> > I create a Name as Test = {"A" , "B"} (not a range but a list of
>> >>> > datas)
>> >>> > by the menu 'Insert" / "Name" / "define"
>> >>> >
>> >>> > Then i cant'use that name "Test" in the Data validation Combo box
>> >>> > by the menu "Datas" / "Validation" / "Option" / "Autorize" (List)
>> >>> > The error message is that system does'nt accept in "Source" = Test
>> >>> >
>> >>> > Where is the Probleme
>> >>> >
>> >>> > thanks for your help
>> >>> >
>> >>> > Gilles PROVOST
>> >>> 
0
Roger
2/5/2008 4:45:17 PM
Reply:

Similar Artilces:

Excel template that lists each month's meetings w/ some detail
I'm looking for an Excel template that lists every month of the year with room to list weekly, monthly and quarterly meetings, including some detail for each meeting. Probably would need to be landscape oriented. The main intent is to give the person using it an idea of upcoming meetings, the intent of each and what prep is required. ...

the user authentication passed to the platform is not valid 09-20-06
Anyone receive this error message in the CRM Outlook Client? "The user authentication passed to the platform is not valid." Thanks, Scott ...

Ho to make one field required based on critera of another field?
I'm creating a form and need to make the "comments" field required if the "code" field is =>20. I appreciate suggestions! Deadline Monster is lurking! User enters the job processing endcode value (numeric) into the "code" field. If the endcode is =>20, comments are required. (P.S. I don't know VB) Thanks! Star You would put your validation code in the Form's BeforeUpdate event. If Me.EndCode >19 Then If Len(Me.Comments & "") = 0 Then MsgBox "Comments are required" Cancel = True End If End If ...

Passing data from one form to another
Hello I have a form called frmMaindB and it has 5 text boxes on it (txtEmployeeTime, txtDTRegular, txtDTReason1, txtDTReason2, txtDTMaintenance) when I double click on the text box it opens up a pop up form named frm_DecimalConversion. On this form I have two text boxes one box I enter data into and the other calculates or converts the data to a decimal. The box that converts the data is called txtDecimal. Then I have a close button which I want to use to close the pop up form and insert the data into the text box I double clicked in to get the pop up or (frm_DecimalConversion). I have r...

data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...

hyperlinking to an item in Global Address List Options
I have had success linking via html from web page to opening a specific contact record from Microsoft Outlook. I have done it like this: outlook://Big%20Contacts/Contacts/~Peter%20Crawford However, my very large contact database also has people with basically the same name, like John Smith. In the current "Address Card" view inside Microsoft Outlook, one of the John Smith's is always sorted first. If I enter outlook://Big%20Contacts/Contacts/~John%20Smith the first John Smith in the sort order is always opened. However, I want to specify one of the other John Smith's....

Change outbound server in header to fix 550 Can't verify your host name error
The headers on the outbound emails show the internal DNS name of our exchange server; obviously this won't resolve properly at the destination. How/where in Exchange 5.5 can I force the IMC to use a real fqdn on outbound mail? Thanks! Frinky You can do this in TCP/IP properties\Advanced\DNS tab of machine. And yes, this is not just for Exchange, so you may consider forwarding all outgoing mail to some relay server (your firewall or ISP's server). Professor Frink wrote: > The headers on the outbound emails show the internal DNS name of our > exchange server; obviously this...

How do I set the number format to Base 12?
I would like to change the number format on my spreadsheet from Base 10 to Base 12, eg. 12 bottles makes up 1 case. Therefore, if I were adding up three different cells 9 bottles + 11 bottles + 6 bottles, my result should be 2 cases 2 bottles if possible 2.2 in a case column. See http://www.cpearson.com/excel/fractional.htm for details. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Andrew Moore" <AndrewMoore@discussions.microsoft.com> wrote in message news:893CABE9-37D7-4E6B-8A7E-A5E679C8C824@microsoft.com... >...

display name #2
hi i have this problem: when i send a mail massage from an application by exchange 2003, in outlook i see the e-mail address (xx@mydomain .com) and not display name (Customer Care) how can i resolve it? Thanks An application should allow you to specify display name for sender. Does it send via SMTP or MAPI? Matrix_pd wrote: > hi > i have this problem: when i send a mail massage from an application by > exchange 2003, in outlook i see the e-mail address (xx@mydomain .com) and > not display name (Customer Care) > > how can i resolve it? > > Thanks -- Please re...

Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...

How can I stop charts from refreshing when changing source data?
My problem is, that I am working with a lot of data and when I change some of the ranges all charts in my view refreshes and it takes much time. My pc is aP4 3GHz, 2GB RAM so that should not be the bottleneck. Is there any way to force the charts not to update all the time? ...

Conditional Formatting w/ a List/Icons
I am trying to allow someone to select "Green", "Yellow" or "Red" from a list and the cell to display a green/yellow/red icon appropriately. Or, if possible, the user could just select the icon (instead of selecting text). Is this possible? Use Data Validation for the list. Type in Red, Yellow, Green as the list. This give the user the list to select from. Use Conditional Formatting for the fill part. Set three conditions, If Cell Value-"Green" (select a green fill), etc.. -- If this helps, please remember to click yes. "...

how do I remove fx from the function line, can't enter data
I have the fx displayed just under my toolbar, and I can't enter or change data in any of the cells in the file. I can't get the red X, the Green check mark, or the black = sign to appear. There are very few areas that are not "greyed out" under the headings at the top. This situation applies to all of the excel files on this computer. I have Excel 2000. Please help. Can you move the cursor around anywhere in the spreadsheet? "dmdranch" wrote: > I have the fx displayed just under my toolbar, and I can't enter or change > data in any of the c...

automatically naming files for savingb
Hi, for delivery notes i would like excel to look in the relative directory for a given customer, ie if C4 contains superspecs, files relating to this account would be in c:\work\superspecs\ files are named superspecsdel0001,superspecsdel0002.......etc superspecsinv0001...etc for delivery notes and invoices. what i would like is a line (or more) to add to a macro which will look in the appropriate directory, add 1 to the last four digits of the last file, enter this number into a cell on the sheet and save the new file with the appropriate name, then close the file. is this possible, or alte...

Save As using contents of Cell in Name
Can someone help with code that changes the "save As" file name to combination of the original File Name + the contents of a cell, an saves file in same folder as the original. E.G. Original File Name= Timesheet Cells B1= Location Cell C1 = Date in format 3-5-2004 Cell A1 = concatenate(B1,C1) Fle Name will therefore be "Timesheet Location 3-5-2004" The other problem is with the date. When concatenating, how do I forma the date to avoid it looking like "Location 38051" Any help much appreciated Nic -- Message posted from http://www.ExcelForum.com Hi for c...

how do i recover data in publisher
i have been entering addresses to set up a mail merge. i cllicked the "ok" button in the window and lost all data . can i recover it Look in a folder in My Documents named "My Data Sources". Publisher data is saved as .mdb(Access) file. Did you try selecting "Edit Address List" in the Mailings and Catalog menu (Tools)? -- Mary Sauer http://msauer.mvps.org/ "dee" <dee@discussions.microsoft.com> wrote in message news:690430F1-36DE-47EE-8B7D-DD12A096C075@microsoft.com... >i have been entering addresses to set up a mail merge. i cllicked ...

formula to get sheet name
hi, can a formula retreive a worksheet name or does it need to be macro based? thanks, N.S. Hi Nigel, This formula that takes care of the possible situation whereby you have only one sheet in the workbook and its name is the same as the workbook's. Note: CELL("filename") will only work if the file has been saved at least once and if the file is opened in a different language system, the argument "filename" will need to be changed manually to the corresponding word (e.g. in Spanish "nombrearchivo"): =LOOKUP(REPT("z",255),SUBSTITUTE(MID(CE...

Using subtotals as single data entries
Sorry about the subject--I couldn't figure out how to describe it simply. I have a large file (16,000 records) of amounts billed by roughly 10,000 service providers. A number of these providers have multiple office locations, so each record is unique to a specific office location. In other words, a provider who billed from 3 different office locations will have 3 entries. Each provider has a unique provider ID number, which stays the same regardless of which office location he is billing from. I want to be able to subtotal the amount billed by each provider for all their office locations...

How can I keep track of when (date and time) data is entered into.
I am trying to create a spreadsheet for a high school class. I need to be able to track when a student has entered data into specific cells of the spreadsheet. Any ideas? In the code behind the worksheet, enter (eg) Private Sub Worksheet_Change(ByVal Target As Range) Cells(1, 1).Value = Now() End Sub This will enter in Cell A1 the date and time at which any entry is made in that worksheet. If you need the location of the time-stamp to vary according to which cell is changed then you can test the value of Target and vary the destination cell accordingly. -- Return email address is n...

Smart List
This is a multi-part message in MIME format. ------=_NextPart_000_002C_01C4A796.1EC1D910 Content-Type: multipart/alternative; boundary="----=_NextPart_001_002D_01C4A796.1EC1D910" ------=_NextPart_001_002D_01C4A796.1EC1D910 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable BlankIs there any way to get a report in smart list that includes the = serial numbers using inventory transactions or sales transcations? I = have version 7.0 Lisa ------=_NextPart_001_002D_01C4A796.1EC1D910 Content-Type: text/html; charset="Wind...

How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Hi This is driving me ABSOLUTELY NUTS! How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep the columns (something that doesnt happen if I paste out of FireFox, fwiw). But it pastes with all the formatting & URLs etc - which I DONT WANT! OK, I can save as .CSV, close, 2 warnings, and re-open but when done REPEATEDLY this is a damned nuicance! Any suggestions? Ship Shiperton Henethe ship w...

OL2003 Master Category List
Anyone know where the master category list is hiding these days. In 2002 it was hkcu\software\microsoft\office\10 \outlook\categories Any help would be appreciated. change 10 to 11. It won't exist unless the user has added something to it and like Outlook 2002 it's a Unicode list so the value is a binary and not a string. -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Author: Absolute Beginners Guide to Microsoft Office Outlook 2003 Reminder Manager, Extended Reminders, Attachment Options http://www.slovaktech.com/products.htm "Dan" <anonymous@discussions.m...

Retrieving sorted data from same table.
Hi All, I am working on a table (mentioned below) I am looking for a query which can get me the data according to the =93id=94 column with respect to speed. The condition is that I have to get three consecutive entries which have speed > 60 Below is the sample table with data on which I have to retrieve the data on above condition. The output i need can be as given below DVXC002 12/10/09 0:12 96 DVXC002 12/10/09 18:40 89 DVXC002 12/10/09 19:43 65 DVXC005 12/10/09 11:56 69 DVXC005 12/10/09 15:26 62 DVXC005 12/10/09 17:35 85 Need your help urgently....Thanks in advan...

change exchange name suffix
Hi We need to change our exchange server 'name' suffix. The situation is that we have a local DNS domain 'bs.si' where all our company computers and server are (Win2k srv AD) We have also external domain 'bs-group.si' that is also for delivering mail (mx record). The Exchange server name is 'posta' Now when someone connects to Exchange server from outside via port 25 it responds like '220 posta.bs.si ...', but I want that the response will be like '220 posta.bs-group.si'. So that mail that is coming from our server will be seen like that i...

Import directory data into Excel 2003
I have over 1000 media files that I would like to extract information from and put into an Excel spreadsheet. Using Explorer, I have defined the fields I would like to see, such as title, duration, comment etc. Now, I need to import this data into Excel. So far, I've not been able to find a way to do this. Can someone offer some suggestions please? Thanks, Nigel -- www.myoldcontacts.com - Tell your friends to tell their friends www.sysadmininc.com - Consultancy, Service, Sales, Networking... www.british-expats.com - Connect with British Expats World Wide www.kxez.com/shows_britishinv...