A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Drop Down List



 
 
Thread Tools Display Modes
  #1  
Old April 22nd, 2009, 08:40 PM posted to microsoft.public.excel.worksheet.functions
katiapro93
external usenet poster
 
Posts: 5
Default Drop Down List

Hi, can anyone help

Is there anyway I can create a drop down list on a form using data that is
located in another workbook yet not have to open it everytime I want to use
the form.

Maybe there is a function that does the same thing? i.e. gets data from a
column and makes a list so you can click a selection.
  #2  
Old April 22nd, 2009, 09:19 PM posted to microsoft.public.excel.worksheet.functions
CK
external usenet poster
 
Posts: 142
Default Drop Down List

Funny, I just learned this today again.

1) First make your list in the other workbook.

2) Highlight the list you want to show in the drop down

3) In the upper right corner, exactly above the Column A title, you'll see a
the cell row/column of the first cell you highlighted. click on that box,
then type a title or name (such as "Menu" - don't need quotes though). PRESS
ENTER!!

Then

4) Go to workbook you want drop down menu to appear on; click on a open cell

5) Open VALIDATION, in the DATA menu

6) In ALLOW: click on what you want to see. If you have a list of options
such as: good, bad, ugly... Then you click on LIST. Make sure both boxes are
checked - Ignore blank and In-cell dropdown.

7) Then in SOURCE: type this =Menu YOU MUST ADD THE EQUAL SIGN for this to
work from another notebook.

8) Click Okay; You can cut and past this new pull down cell anywhere else
in your workbook and the pull down will still work properly.


Hope this helps,

"katiapro93" wrote:

Hi, can anyone help

Is there anyway I can create a drop down list on a form using data that is
located in another workbook yet not have to open it everytime I want to use
the form.

Maybe there is a function that does the same thing? i.e. gets data from a
column and makes a list so you can click a selection.

  #3  
Old April 22nd, 2009, 10:30 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Drop Down List

CK

OP wanted to have a list source from a closed workbook.

Cannot be done.

Quoted from Debra Dalgleish's site.................................

You can use a list from another workbook as the source for a Data Validation
dropdown list.

For data validation to work, the workbook which contains the list must be
open, in the same instance of Excel. You could create the list in a workbook
that is always open, but hidden, such as the Personal.xls workbook.

End quote............................

Also from Excel Help on trouble-shooting DV

Are the referenced cells on the same worksheet or a different workbook?
Cell references have to be to cells on the same worksheet. If you have to
refer to a different worksheet or a different workbook, use a defined name
and make sure the workbook is already open.



Gord Dibben MS Excel MVP


On Wed, 22 Apr 2009 13:19:02 -0700, CK wrote:

Funny, I just learned this today again.

1) First make your list in the other workbook.

2) Highlight the list you want to show in the drop down

3) In the upper right corner, exactly above the Column A title, you'll see a
the cell row/column of the first cell you highlighted. click on that box,
then type a title or name (such as "Menu" - don't need quotes though). PRESS
ENTER!!

Then

4) Go to workbook you want drop down menu to appear on; click on a open cell

5) Open VALIDATION, in the DATA menu

6) In ALLOW: click on what you want to see. If you have a list of options
such as: good, bad, ugly... Then you click on LIST. Make sure both boxes are
checked - Ignore blank and In-cell dropdown.

7) Then in SOURCE: type this =Menu YOU MUST ADD THE EQUAL SIGN for this to
work from another notebook.

8) Click Okay; You can cut and past this new pull down cell anywhere else
in your workbook and the pull down will still work properly.


Hope this helps,

"katiapro93" wrote:

Hi, can anyone help

Is there anyway I can create a drop down list on a form using data that is
located in another workbook yet not have to open it everytime I want to use
the form.

Maybe there is a function that does the same thing? i.e. gets data from a
column and makes a list so you can click a selection.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.