Drop downs from another workbook

[Guest]

I followed the help instructions to get to the point where I could have a
dropdown list from another workbook and at the end I get a n error message
saying "You may not use other worksheets or workbooks for data validation"
The other workbook I am trying to agther from is open too!??????????????????
Please help

 

[Dave Peterson]

Maybe Debra Dalgleish's instructions will help:
http://contextures.com/xlDataVal05.html

 

[Guest]

I checked out the link, thanks for the idea, but I am not sure what all that
OFFSET business is about- I was able to define and name the list from the
open workbook and shouldn't rename the workbook and worksheet in the refers
to box
I considered making the list hidden but that would make it very difficult to
update on every single employees timesheet workbook

 

[Jezebel]

In the book that needs to display the drop-down, define a name (eg ListData)
that refers to the list range in the source book, something like
='[SourceBook.xls]Sheetname'!$A1:$A25

Then select where you want the drop-down to appear; on the validation
dialog, enter the source as =ListData

As you note, this will work only if the source book is open. It is possible
to set up a drop-down list that updates automatically and does not require
the source book to be open, but it's trickier.

 

[Peo Sjoblom]

I think Dave's point was not that you have to create a dynamic range but
that you can use a defined name for your list and then refer to that name,
if your list is in a different workbook you must include the workbook's
name when you define the name just as in the example' Do you have any
problems when you follow the directions?
Personally I know of nobody that can explain Excel better than Debra. The
offset part is only if you want to create a dynamic list that will adapt to
new entries without having to be redefined, it has nothing to do with using
a list from another workbook

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Guest]

Peo & Jezebel:
I have done exactly what you are saying gave it a name- in theis case
ValidType which refers to='[SourceBook.xls]Sheetname'!$A1:$A25- however when
I enter the source of -ValidType I get the error message "You may not use
other worksheets or workbooks for data validation" and yes the sourcebook is
open at the time????
I'm going to try again however I am not sure if it is going to help I did it
6 times last night.
Thanks for your help.

In the book that needs to display the drop-down, define a name (eg ListData)
that refers to the list range in the source book, something like
='[SourceBook.xls]Sheetname'!$A1:$A25

Then select where you want the drop-down to appear; on the validation
dialog, enter the source as =ListData

As you note, this will work only if the source book is open. It is possible
to set up a drop-down list that updates automatically and does not require
the source book to be open, but it's trickier.

I followed the help instructions to get to the point where I could have a
dropdown list from another workbook and at the end I get a n error message
saying "You may not use other worksheets or workbooks for data validation"
The other workbook I am trying to agther from is open
too!??????????????????
Please help

 

[Dave Peterson]

Open up SourceBook.xls
select your range
and give it a nice name.
Insert|Name|define|SValidList
click add

Now back to the other workbook.
Insert|name|define
name: Validtype
refers to: =sourcebook.xls!sValidlist
click add

Now select your cell that gets data|validation.
List
=validtype

Try it again following the instructions at Debra's site. Just ignore that final
step with the =offset() stuff.

Peo & Jezebel:
I have done exactly what you are saying gave it a name- in theis case
ValidType which refers to='[SourceBook.xls]Sheetname'!$A1:$A25- however when
I enter the source of -ValidType I get the error message "You may not use
other worksheets or workbooks for data validation" and yes the sourcebook is
open at the time????
I'm going to try again however I am not sure if it is going to help I did it
6 times last night.
Thanks for your help.

In the book that needs to display the drop-down, define a name (eg ListData)
that refers to the list range in the source book, something like
='[SourceBook.xls]Sheetname'!$A1:$A25

Then select where you want the drop-down to appear; on the validation
dialog, enter the source as =ListData

As you note, this will work only if the source book is open. It is possible
to set up a drop-down list that updates automatically and does not require
the source book to be open, but it's trickier.

I followed the help instructions to get to the point where I could have a
dropdown list from another workbook and at the end I get a n error message
saying "You may not use other worksheets or workbooks for data validation"
The other workbook I am trying to agther from is open
too!??????????????????
Please help

 

[Gord Dibben]

Jenn

Re-read Debra's instructions.

You require two named ranges. The second refers to the first.

1. Named range in source book.

2. Named range in DV workbook which refers to named range in source book.


Gord Dibben Excel MVP

Peo & Jezebel:
I have done exactly what you are saying gave it a name- in theis case
ValidType which refers to='[SourceBook.xls]Sheetname'!$A1:$A25- however when
I enter the source of -ValidType I get the error message "You may not use
other worksheets or workbooks for data validation" and yes the sourcebook is
open at the time????
I'm going to try again however I am not sure if it is going to help I did it
6 times last night.
Thanks for your help.

In the book that needs to display the drop-down, define a name (eg ListData)
that refers to the list range in the source book, something like
='[SourceBook.xls]Sheetname'!$A1:$A25

Then select where you want the drop-down to appear; on the validation
dialog, enter the source as =ListData

As you note, this will work only if the source book is open. It is possible
to set up a drop-down list that updates automatically and does not require
the source book to be open, but it's trickier.

I followed the help instructions to get to the point where I could have a
dropdown list from another workbook and at the end I get a n error message
saying "You may not use other worksheets or workbooks for data validation"
The other workbook I am trying to agther from is open
too!??????????????????
Please help

 

[Guest]

Thank you soooooooooooo much you are my hero !!!!!!!!!!!!
I am so greatful!
I am sending you much good karma

Jenn

Re-read Debra's instructions.

You require two named ranges. The second refers to the first.

1. Named range in source book.

2. Named range in DV workbook which refers to named range in source book.


Gord Dibben Excel MVP

Peo & Jezebel:
I have done exactly what you are saying gave it a name- in theis case
ValidType which refers to='[SourceBook.xls]Sheetname'!$A1:$A25- however when
I enter the source of -ValidType I get the error message "You may not use
other worksheets or workbooks for data validation" and yes the sourcebook is
open at the time????
I'm going to try again however I am not sure if it is going to help I did it
6 times last night.
Thanks for your help.

In the book that needs to display the drop-down, define a name (eg ListData)
that refers to the list range in the source book, something like
='[SourceBook.xls]Sheetname'!$A1:$A25

Then select where you want the drop-down to appear; on the validation
dialog, enter the source as =ListData

As you note, this will work only if the source book is open. It is possible
to set up a drop-down list that updates automatically and does not require
the source book to be open, but it's trickier.





I followed the help instructions to get to the point where I could have a
dropdown list from another workbook and at the end I get a n error message
saying "You may not use other worksheets or workbooks for data validation"
The other workbook I am trying to agther from is open
too!??????????????????
Please help

 

[Gord Dibben]

I'll take all the Karma you can send<g>

Gord

Thank you soooooooooooo much you are my hero !!!!!!!!!!!!
I am so greatful!
I am sending you much good karma

Jenn

Re-read Debra's instructions.

You require two named ranges. The second refers to the first.

1. Named range in source book.

2. Named range in DV workbook which refers to named range in source book.


Gord Dibben Excel MVP

Peo & Jezebel:
I have done exactly what you are saying gave it a name- in theis case
ValidType which refers to='[SourceBook.xls]Sheetname'!$A1:$A25- however when
I enter the source of -ValidType I get the error message "You may not use
other worksheets or workbooks for data validation" and yes the sourcebook is
open at the time????
I'm going to try again however I am not sure if it is going to help I did it
6 times last night.
Thanks for your help.

:

In the book that needs to display the drop-down, define a name (eg ListData)
that refers to the list range in the source book, something like
='[SourceBook.xls]Sheetname'!$A1:$A25

Then select where you want the drop-down to appear; on the validation
dialog, enter the source as =ListData

As you note, this will work only if the source book is open. It is possible
to set up a drop-down list that updates automatically and does not require
the source book to be open, but it's trickier.





I followed the help instructions to get to the point where I could have a
dropdown list from another workbook and at the end I get a n error message
saying "You may not use other worksheets or workbooks for data validation"
The other workbook I am trying to agther from is open
too!??????????????????
Please help