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
323 Views

Similar Articles

[PageSpeed] 2

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:

Some Contacts List messages going into Junk Mail folder
My wife and I use the same computer, and we have our separate email addresses set up in WLM. We also share the Contacts folder. Starting about a month or two ago, many of the messages from my wife's friends, who are in our Contacts folder, have been going repeatedly into her Junk Mail folder. Even clicking of the Not Junk option has failed to stem this flow. I have not had this experience with my email messages. What might we be missing in our setup? Gordon Biggar Hotmail type accounts for both email addresses ? - Hotmail.com, Live.com, Msn.com *Note: All Hotmai...

Can I add a domain to the Blocked Senders list?
I am just getting used to Outlook 2003 and trying to make use of the SP{AM filter. I see that when I right click on a message that one of the options id to add a sender to the "Blocked Senders List" Is there a way that I can also include the option to block a domain? Thanks Dunc Dunc wrote: > Is there a way that I can also include the option to block a domain? There is an easy way, if you're running Outlook 2003+SP1. Dunc wrote: > Is there a way that I can also include the option to block a domain? Right-click on any e-mail, choose (menu) Junk Mail, Junk Mail...

Discount Lists 01-28-05
Hey all, First time posting here! Have an issue with a discount list not applying to a Price List Item. It seems to be applied to the PLI, but when I bring up a quote, and say order 5 of something, they are still at the same unit price as if I ordered 1. Is there another place where I need to define the discount list? Also, is there a good CRM site that lists some FAQs or just a good site on CRM? Thanks! Michael Hi Michael When you open the discount price item under the price list is the discount list specified here? Thats quite a tongue twister! To answer your query on other ...

Formula argument based on text
Hi All, I have cells (A1:A10) populated with text ("Pass" or "Fail"). In cell (A11) I want to have a formula that returns the text: "pass" if all the cells (A1:A10) contain the text "pass" "fail" if any one of the cells (A1:A10) contains the text "fail". How would I write the formula to achieve this? Regards gregork In A11 enter: =IF(COUNTIF(A1:A10,"Fail")>0,"Fail","Pass") HTH "gregork" <gregork@paradise.net.nz> wrote in message news:0RXSb.19911$ws.2664893@news02.tsnz.net... > ...

Locking a Marketing List
I can't find any information about what it means to Lock a marketing list. I've searched the KB, the web, the Help, and the marketing automation training materials, but have not found any references to this and how it's used. What does this mean? Steve, Flagging a Marketing List as "locked" turns off the Manage Members functionality. It prevents you from being able to use the Advanced Find controls for adding, removing and evaluating marketing list members from the marketing list screens as well as selecting records from a list view and adding them to that list. Ho...

Adding Named Formulas to a Chart
Guys I've been trying to figure out how to add a named formula to series' field. Do i just add teh name of the formula in quotes, singl quotes, or what? thanks -- Message posted from http://www.ExcelForum.com Maurice, like this: =workbook.xls!rangename -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com "maurices5000 >" <<maurices5000.178a3w@excelforum-nospam.com> wrote in message news:maurices5000.178a3w@excelforum-nospam.com... > Guys I've been trying to figure out how to add a named formula to a > ...

query-based distribution group not showing in Outlook
Software: Exchange 2003 standard and Outlook 2003 I have created a query-based distribution group and it isn't showing up in Outlook Address Book. If I create Distribution groups they show up in the Address Book. How do I get the query based groups to show in the Address Book? Under Exchange Advanced I have confirmed that the check box to hide list is unchecked. Thanks, Colin M. On Tue, 27 Dec 2005 10:54:52 -0800, "Colin" <legendsfan@nospam.nospam> wrote: >Software: Exchange 2003 standard and Outlook 2003 > >I have created a query-based distribution ...

Combo Box from data in another tab
Is it possible to create a combo box from data that's in another tab? I have a combo box that's from data in hidden columns. But now I need to move all that hidden data to another tab in the same file. But when I go into properties and go to the ListFillRange option, it won't let me enter another tab name. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200508/1 I have called data from another tab with a combo box by doing th following: First - name the range of the data on the other tab. This can be don with the shortcut on the toolbar (l...

Adding a new data series to an existing chart
I have a stacked bar chart that shows monthly sales by customer type. I want to add an additional data series to show the monthly forecast, to be displayed as a point or line against the existing stacked-bar data. Any ideas? Select the data you want to plot. Drag onto the chart. XL will pop up with a dialog box asking for some information. Provide it and you will be all set. -- Regards, Tushar Mehta MS MVP Excel 2000-2004 www.tushar-mehta.com Excel, PowerPoint, and VBA tutorials and add-ins Custom Productivity Solutions leveraging MS Office In article <1418C2D7-4DF6-4945-A08A...

storing multiple list selections in access
i have 2 tables, table a and table b. is it possibile for a control bound to table a (a list box, with multiple selections eneabled) to be able to select multiple records from table b, and store them? i.e. table A record 1 has table B records 1,2,3,4,8,12, and so on selected while Table A record 2 only has table B records 3, 8 and 15 selected, and so on.. is there some way to store those selections within table A? for those curious, i'm making a character sheet for an RPG.. table A is the actual sheet, while B is the list of spells.. i'm looking for a way to store which spells each ...

How do I change the value in cell based on a future date
I would like certain cells to be cleared after a certain date one way: Put this in the ThisWorkbook code module (Right-click the workbook title bar and choose View Code): Public Sub Workbook_Open() If Date > #12/15/2004# Then _ Sheets("Sheet1").Range("A1,B2,J10,K39").ClearContents End Sub Note that this won't work if the user opens your workbook with macros disabled. Note also that there's no way to prevent a user from accessing your data - i.e., they can set their clock back and open the file. You can make things more difficu...

How to find a value with multi-column, multi-record list
I have a spreadsheet with $costs arranged according to values in both rows and columns. How do I create drop down lists for both the rows and columns and then return the cell value? eg if I have 5 rows (2-6) and 6 columns (B-G) with costs arranged in each cell within this group how do I create drop downs for each selection, 2-6 and B-G, and return the value to a selected cell. In a typical table of this nature there would be descriptive row and column headers. To do a lookup on this table you would then look for the intersection of a specific row header and a specific column ...

Excel 2000
Hi! *First off:* I have created a spreadsheet that has a dynamic range an data validation. *Next:* i know i can add options to the 'dynamic range' and my dro down menu will add the option to its list. *Problem:* How can I make it so the user can add data into th validated data list/drop down menu rather than having the user addin it into the dynamic range? *Example:*this drop down menu indicates how often a system is updated weekly, monthly, etc. etc... i don't want to restrict it, so i want t make it so they can indicate their own time on how often those update occur. TIA -...

How do I force all capital in a column of lower case names?
I read the help, but it doesn't tell me where to enter the formula. If you do not have a blank column next to the mentioned column, then insert one. Enter the formula in a cell adjacent to the cell you want to change, and copy down. Then copy this column, go to the original column, select a cel, right click and click on Paste Special, tick values. Delete the helper column. Iow, with you lower case data in Column E, click on Column F, if not empty, and insert a column. F is then an empty column. Say your data starts in E2, then in F2 enter =UPPER(E2). Move cursor to the botto...

Data within a cell
If names are in cells as "last name, first name, middle initial" can a macro be run to change the order to "first name, middle initial, last name"? A formula will do Assuming you have spaces between commas and names, =MID(A2,FIND(",",A2)+2,FIND(",",A2,FIND(",",A2)))&RIGHT(A2,FIND(",",A2,FIND(",",A2)))&", "&LEFT(A2,FIND(",",A2)-1) Then just copy down. If you want this more permanent, copy the cells with formulas, right click, paste special. -- Best Regards, Luke M *Remember to click &q...

How do I make the x axis data the y axis data?
My graph automatically makes certain data the y axis and other data the x axis. I need to just reverse it for the graph I am looking for. Any suggestions? SLG, One option is to reverse the positions of the data on the spreadsheet. Assuming an XY chart, you would change a setup like this: x y 5 4 4 2 5 3 7 4 6 5 to this: y x 4 5 2 4 3 5 4 7 5 6 If this doesn't work, can you post back to let us know what type of chart and data you're using? ---- Regards, John Mansfield http://www.pdbook.com "SLG" wrote: > My graph automatically makes certain data the y axis ...

Data entered from list automatically enters number in another cell
I am using Excel 2007 and here is an simplified example of what I need. Items Price Potatoes 4.35 Apples 5.55 Oranges 7.95 Onions 4.55 Carrots 3.75 Items Column is List for Valid entries in table below Prices are the numbers I want entered when I enter the Item If I enter Oranges from Dropdown List in A16 , I want Excel to automatically enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, just 1 column over in cell B16 Example of table I want: My Entries: Excel Enters ...

Prevent auto recoloring of chart when using Pivot Table data
I have crated a Pivot tabel with data I want to show in a Chart. My problem is when I refresh data the colours of the "data series" changes. For exaple I want one of my series "OK" always be shown in green colour, but after a refresh of pivot all colours changes. This problem is not only colouring. If I have made changes to my chart so that one series ar plotted on a secondary axis, and I change it to another chart type (e.g. the secondary should be shown as a line instead of column) this also changes when refreshing the data. Most irritating! Anyone that knows how to p...

converting plain data to table format
It has been a while since I used excel but I am almost sure there is a way to convert just plain rows and columns of data into a table format. This way I believe that adding rows to this block of data is as simple as tabbing after the last cell and it should just drop down to the next row and insert one??? correct?? Excel should recognize a contiguous range as a table, or list. Include headings in the first row, and leave at least one blank row and column between the table and other items on the worksheet. You can use the built-in data form to add records to the table (Data>Form). Or,...

Data Migration Framework 08-04-04
Hi, I am not understanding something pretty fundamental regarding the Data Migration Framework (DMF). I am looking to migrate a limited number of records (approx 1K) from Goldmine into MS CRM as Account records. I understand that the DTS in SQL can be used to load the data into the CDF database. On page 73 of the Data Migration Framework Guide (v1.2) it states that you use the Goldmine export utility to extract data from Goldmine. However, how do you use DTS to load this data into the CDF database as Goldmine exports it into Excel? Also, using the export facility in Goldmine you can only e...

imported distributions lists show as contacts
Client exported his Contacts containing several Distribution Lists, then imported into a new system. Teh imported DL's show up as separate Contact Lists...how, why, and more importantly, is there a simplified way to correct? Version of Outlook they were exported from and imported into? How did you import them? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net ...

'error PRJ0019: A tool returned an error code from "Performing registration" Project Name
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C80055.07F02D20 Content-Type: text/plain; charset="gb2312" Content-Transfer-Encoding: quoted-printable Hi, all I wrote a COM project with VS2003, when I compile this project = ,finally I get the error like 'error PRJ0019: A tool returned an error = code from "Performing registration" Project Name. Any helpful ideas would be highly appreciated! -ja ------=_NextPart_000_0006_01C80055.07F02D20 Content-Type: text/html; charset="gb2312" Content-Transfer-Encoding: quoted-printable...

Sorting lists side by side to match variables
Hello everyone, I have a problem that I need to find a solution for. I have a list o all the stores with a particular trait that carry my at least one of m items. Then, I have a list for each item with all the stores tha carry that particular item. What I want to do is sort this data s that the store numbers under each item are matched up with th corresponding store numbers in the master store list. I'll give you a example of the problem. Here is what the data looks like now: Master Item 1 Item 2 Item 3 Item 4 1 2 3 1 3 2 4 2 4 3 4 4 And here is what I would like it to look...

Fully qualified domain name change
When I first installed Win2003 and Exchange we didn't have a fully qualified domain name yet so the installation of Exchange used the email format of "sender@company.local". While this is OK for internal email external email cannot recognize company.local. We now have a fully qualified domain name so how does one change the current "company.local" email addresses to "company.com" addresses? Thanks and I apologize in advance if this is a stupid question. -- Gyro You need to 'apply' a new RUS policy to add the new e-mail addresses to each mai...

List of File Extensions
Is there a list somewhere that contains the file extensions used in Office 2003 applications? I need to backup files before re-installing my OS. These include files that store settings, customizations, macros, Outlook address books, etc. ...