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?
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?
>
>
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?
> >
> >
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?
> > >
> > >
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?
> > > >
> > > >
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?
> > > > >
> > > > >
>
>
Hi, you only get the error "needs to be a delimited list" if you have tried to create a validation list that covers more than one column (or at least thats what i have found!), your list needs to refer to a named range that stays within one column you can have as many blanks as you 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 cell contents (I think!) and starts your list at the bottom where all the blanks are, so if it was at the top of the list it would start there!
Hope this helps you,
Regards,
Simon
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?
> >
> >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks