Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a List
Excel 2003
Hello, I am trying to create a drop-down list in a workbook based on a range of cells in another workbook via the Data, Validation menu commands. I have followed the steps as specified in the Help system which state that I can reference a "different worksheet in a different workbook." However when I try to create the list I receive the message that you cannot use external references (I am using a named range created in the workbook that references the external cells). Thanks for any help you can provide. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a List
Not sure about using data validation for this, but you could use a
combo box to select from a list that lives in a different workbook. Jim Jeff Bendert wrote: Excel 2003 Hello, I am trying to create a drop-down list in a workbook based on a range of cells in another workbook via the Data, Validation menu commands. I have followed the steps as specified in the Help system which state that I can reference a "different worksheet in a different workbook." However when I try to create the list I receive the message that you cannot use external references (I am using a named range created in the workbook that references the external cells). Thanks for any help you can provide. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a List
How would I do that?
"jim" wrote: Not sure about using data validation for this, but you could use a combo box to select from a list that lives in a different workbook. Jim Jeff Bendert wrote: Excel 2003 Hello, I am trying to create a drop-down list in a workbook based on a range of cells in another workbook via the Data, Validation menu commands. I have followed the steps as specified in the Help system which state that I can reference a "different worksheet in a different workbook." However when I try to create the list I receive the message that you cannot use external references (I am using a named range created in the workbook that references the external cells). Thanks for any help you can provide. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a List
You can get more information by reading up on Excel Forms, but this
should get you started. Also, this will only work if you want a single menu (vs. the same menu appearing in multiple cells*). First, show the forms toolbar: View Toolbars Forms Then get your menu started: -Click on the "Combo Box" icon (mouse over the new toolbar until this comes up) -Click and drag (just like a text box) into your workbook -Right click the menu and choose "Format Control" -For the "input range" select the data in the workbook you want to appear in the list -For the output range, choose a cell in the existing workbook you can reference -Specify the number of rows you want to appear in the list -Then Okay out of it When you select from the menu, the output range will change to a numeric (that can be used in conjunction with a vlookup against the list if you need to translate to some other value). *If you want several cells to contain the list, you might want to find a copy to copy the list from your other workbook into the active one (into hidden cells?) and then stick with the validation option you originally tried, but within the same book now. At least that's a couple of ideas anyway. Jim Jeff Bendert wrote: How would I do that? "jim" wrote: Not sure about using data validation for this, but you could use a combo box to select from a list that lives in a different workbook. Jim Jeff Bendert wrote: Excel 2003 Hello, I am trying to create a drop-down list in a workbook based on a range of cells in another workbook via the Data, Validation menu commands. I have followed the steps as specified in the Help system which state that I can reference a "different worksheet in a different workbook." However when I try to create the list I receive the message that you cannot use external references (I am using a named range created in the workbook that references the external cells). Thanks for any help you can provide. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a List
Good day Jim.
First this link will provide you a visual plus a good starting point. http://www.contextures.on.ca/xlDataVal05.html Second here is a wriiten procedure if you are running Excel 2003: Ensure that you have "BOTH" Workbooks open in order for the Data|Validation|Allow|List source to function properly. Assign a 'Name' to the 'List' that was created in the source Workbook that the target Workbook can read. To accomplish this, open 'target' Workbook, Use the following menu items: Insert|Name|Define In the "Names In Workbook" box, enter a name of your chosen. Ex: ValidDepts Select the *entire* contents of the "Refers To" box (turns black), And switch to the source Workbook, and select the range of cells that contains your data list. Very important item he Backspace until the explanation point. Now enter the 'Name' range of the 'List' Click <OK NOW... set-up your drop-down list. Select the cell to which you desire your drop-down list and use the following menu items: Data|Validation|Allow click on 'List'. In the "Source" box, enter your chosen name: Ex: =ValidDepts (Ensure that you use the equal sign) Click <OK And you're done! Tony D. Abel "Excellence is an art won by training and habitation. We do not act rightly because we have virtue or excellence, but rather we have those because we have acted rightly. We are what we repeatedly do. Excellence, then is not an act but a habit." "jim" wrote: You can get more information by reading up on Excel Forms, but this should get you started. Also, this will only work if you want a single menu (vs. the same menu appearing in multiple cells*). First, show the forms toolbar: View Toolbars Forms Then get your menu started: -Click on the "Combo Box" icon (mouse over the new toolbar until this comes up) -Click and drag (just like a text box) into your workbook -Right click the menu and choose "Format Control" -For the "input range" select the data in the workbook you want to appear in the list -For the output range, choose a cell in the existing workbook you can reference -Specify the number of rows you want to appear in the list -Then Okay out of it When you select from the menu, the output range will change to a numeric (that can be used in conjunction with a vlookup against the list if you need to translate to some other value). *If you want several cells to contain the list, you might want to find a copy to copy the list from your other workbook into the active one (into hidden cells?) and then stick with the validation option you originally tried, but within the same book now. At least that's a couple of ideas anyway. Jim Jeff Bendert wrote: How would I do that? "jim" wrote: Not sure about using data validation for this, but you could use a combo box to select from a list that lives in a different workbook. Jim Jeff Bendert wrote: Excel 2003 Hello, I am trying to create a drop-down list in a workbook based on a range of cells in another workbook via the Data, Validation menu commands. I have followed the steps as specified in the Help system which state that I can reference a "different worksheet in a different workbook." However when I try to create the list I receive the message that you cannot use external references (I am using a named range created in the workbook that references the external cells). Thanks for any help you can provide. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help needed - creating invoice from packing list (both in excel) | Excel Discussion (Misc queries) | |||
Help needed - creating invoice from packing list (both in excel) | Excel Discussion (Misc queries) | |||
Creating a List based on your choice from Another List | Excel Discussion (Misc queries) | |||
question about creating a simple drop down list | Excel Worksheet Functions | |||
Creating an invoice with a lookup list | Excel Discussion (Misc queries) |