Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dat Validation Different Sheet
We all know you can create a drop down list in excel where you put in
the values for the source using certain selected cells or you can type it in yourself. How do you select different cells on a different sheet? It just wont let me go to another worksheet when i try changing worksheets. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dat Validation Different Sheet
create a defined name/named range refering to your other sheet and use that
in the source Insert = Name = define Name: List1 Refers to: =Sheet3!$A$1:$A$10 click the ADD button. then in the data validation in the list source textbox =List1 the equal sign is important. -- Regards, Tom Ogilvy "RigasMinho" wrote: We all know you can create a drop down list in excel where you put in the values for the source using certain selected cells or you can type it in yourself. How do you select different cells on a different sheet? It just wont let me go to another worksheet when i try changing worksheets. Any ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dat Validation Different Sheet
Nice - thanks man.
Another question - what if i have two rows that i define and name as say: Test1 and Test2 When i go to the source part I would type in: =Test1,Test2 but it gives me an error of unions, arrays can not be done in source. Any work around way? Tom Ogilvy wrote: create a defined name/named range refering to your other sheet and use that in the source Insert = Name = define Name: List1 Refers to: =Sheet3!$A$1:$A$10 click the ADD button. then in the data validation in the list source textbox =List1 the equal sign is important. -- Regards, Tom Ogilvy "RigasMinho" wrote: We all know you can create a drop down list in excel where you put in the values for the source using certain selected cells or you can type it in yourself. How do you select different cells on a different sheet? It just wont let me go to another worksheet when i try changing worksheets. Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dat Validation Different Sheet
I also tried - doing this:
define / name test1 = to certain cells, skip some lines, then more cells. So it would basically be: =Welcome!$I$2:$I$17,Welcome!$I$19:$I$35 notice the gap from 17-18 but same column When i try to name the source become test1 after trying this it then says "cells have to be in a single row with no spaces" RigasMinho wrote: Nice - thanks man. Another question - what if i have two rows that i define and name as say: Test1 and Test2 When i go to the source part I would type in: =Test1,Test2 but it gives me an error of unions, arrays can not be done in source. Any work around way? Tom Ogilvy wrote: create a defined name/named range refering to your other sheet and use that in the source Insert = Name = define Name: List1 Refers to: =Sheet3!$A$1:$A$10 click the ADD button. then in the data validation in the list source textbox =List1 the equal sign is important. -- Regards, Tom Ogilvy "RigasMinho" wrote: We all know you can create a drop down list in excel where you put in the values for the source using certain selected cells or you can type it in yourself. How do you select different cells on a different sheet? It just wont let me go to another worksheet when i try changing worksheets. Any ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dat Validation Different Sheet
Also is there a way to make the source of list contain values that are
blank? I selected cells that are blank and it gives me an error of "Source has to be a delimited list" RigasMinho wrote: I also tried - doing this: define / name test1 = to certain cells, skip some lines, then more cells. So it would basically be: =Welcome!$I$2:$I$17,Welcome!$I$19:$I$35 notice the gap from 17-18 but same column When i try to name the source become test1 after trying this it then says "cells have to be in a single row with no spaces" RigasMinho wrote: Nice - thanks man. Another question - what if i have two rows that i define and name as say: Test1 and Test2 When i go to the source part I would type in: =Test1,Test2 but it gives me an error of unions, arrays can not be done in source. Any work around way? Tom Ogilvy wrote: create a defined name/named range refering to your other sheet and use that in the source Insert = Name = define Name: List1 Refers to: =Sheet3!$A$1:$A$10 click the ADD button. then in the data validation in the list source textbox =List1 the equal sign is important. -- Regards, Tom Ogilvy "RigasMinho" wrote: We all know you can create a drop down list in excel where you put in the values for the source using certain selected cells or you can type it in yourself. How do you select different cells on a different sheet? It just wont let me go to another worksheet when i try changing worksheets. Any ideas? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dat Validation Different Sheet
as far as I know, the source range has to be a single contiguous range.
You can possibly use another range on the sheet to build such a contiguous list. Blank cells in that contiguous range should cause no problems. -- Regards, Tom Ogilvy "RigasMinho" wrote: Also is there a way to make the source of list contain values that are blank? I selected cells that are blank and it gives me an error of "Source has to be a delimited list" RigasMinho wrote: I also tried - doing this: define / name test1 = to certain cells, skip some lines, then more cells. So it would basically be: =Welcome!$I$2:$I$17,Welcome!$I$19:$I$35 notice the gap from 17-18 but same column When i try to name the source become test1 after trying this it then says "cells have to be in a single row with no spaces" RigasMinho wrote: Nice - thanks man. Another question - what if i have two rows that i define and name as say: Test1 and Test2 When i go to the source part I would type in: =Test1,Test2 but it gives me an error of unions, arrays can not be done in source. Any work around way? Tom Ogilvy wrote: create a defined name/named range refering to your other sheet and use that in the source Insert = Name = define Name: List1 Refers to: =Sheet3!$A$1:$A$10 click the ADD button. then in the data validation in the list source textbox =List1 the equal sign is important. -- Regards, Tom Ogilvy "RigasMinho" wrote: We all know you can create a drop down list in excel where you put in the values for the source using certain selected cells or you can type it in yourself. How do you select different cells on a different sheet? It just wont let me go to another worksheet when i try changing worksheets. Any ideas? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dat Validation Different Sheet
Hi, you only get the error "needs to be a delimited list" if you hav tried to create a validation list that covers more than one column (o at least thats what i have found!), your list needs to refer to a name range that stays within one column you can have as many blanks as yo like but i suggest you have one blank only at the top of your list because when you first click the drop down it matches the curent cel contents (I think!) and starts your list at the bottom where all th blanks are, so if it was at the top of the list it would start there! Hope this helps you, Regards, Simo -- Simon Lloy ----------------------------------------------------------------------- Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670 View this thread: http://www.excelforum.com/showthread.php?threadid=56677 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dat Validation Different Sheet
This works for a different worksheet in the same Excel file. However, when I
tried to follow the Help instructions to create a defined name referring to cells in a different file, I get 'You may not use references to other worksheets or workbooks for Data Validation Criteria'. Any idea what's going on? BOth files are open and are in the same folder. What I ultimately want to achieve is this, and feel free to tell me that data validation through a separate file is not the best way, in which case please inform me a better way: I am asking a number of people to record their daily data processing activities as part of a productivity study. Only about 10 people will do some recording for about two weeks. It's NOT an IT project. I don't want to prescribe the kind of activities they are doing, but I do want consistent entry terminologies. Thus I don't want to see 'social security number' and 'SSN' as both data type entries. so I created a drop down list in the 'data involved' column so they can pick from the drop down list. Each staff will fill out his/her own spreadsheet as separate files, so that they don't mess up with other people's entries. To allow them to add new types of data processed and at the same time disallow redundant new type creations, I created a single ValidDataInputTYpes.xls spreadsheet and try to have the drop downlists refer to the list of valid items in that separate file. Thus all the people have access to the single ValidDataInputTypes.xls and can add new data types there. If somebody sees 'SSN" already created, then he/she won't add another Social Security Number there. With multiple users accessing the same file, I am now getting into the synchronization issue. Given the low volume of data involved, and no IT product will come out of this, I can tolerate a few mistakes and I will simply address them by hand. Please let me know what's the easiest way to achieve what I want to do. If data validation through external file is the way to go and Excel Helps says I can, how come it doesn't work? "Tom Ogilvy" wrote: create a defined name/named range refering to your other sheet and use that in the source Insert = Name = define Name: List1 Refers to: =Sheet3!$A$1:$A$10 click the ADD button. then in the data validation in the list source textbox =List1 the equal sign is important. -- Regards, Tom Ogilvy "RigasMinho" wrote: We all know you can create a drop down list in excel where you put in the values for the source using certain selected cells or you can type it in yourself. How do you select different cells on a different sheet? It just wont let me go to another worksheet when i try changing worksheets. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation in another sheet | Excel Discussion (Misc queries) | |||
Validation,........how to use a list in another sheet | Excel Worksheet Functions | |||
data validation on other sheet | Excel Programming | |||
Validation from another sheet | Excel Programming | |||
Add validation using sheet code | Excel Programming |