10

In Excel (2003), I want to limit the selection of values in a cell to allowable values from another spreadsheet.

For example, in the sheet "Currencies" I have

EUR,1.1
GBP,1.0
USD,1.5

(That's two columns, three rows)

In my main sheet I'd like to have a Currency column, in which the only allowable values are from column A in the Currencies sheet, i.e. the cell can contain only 'EUR', 'GBP', or 'USD'. (Elsewhere I'll be using that as a lookup to get the exchange rate, which is column B.)

How can I restrict the value in the cell? Having a dropdown like a windows combo box populated from the other sheet would be great.

Any answers using formulas, VBA, or whatever will be fine. Even a pointer to the relevant documentation would be great (I have looked, but I'm not sure what to look for).

Also helpful would be answers using LibreOffice, since I haven't finalized the choice of spreadsheet program, but I thought I'd have a better chance getting an answer in Excel.

Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
Randy Orrison
  • 1,259
  • 2
  • 15
  • 14

2 Answers2

14

VBA isn't necessary in this case. Use the built-in "Data Validation" feature.

Select the cell you want to add the combo box to, and choose Data, Validation.

Select "List" as the option, and either put a cell range in the "Source" box, or a comma-delimited list of values (if you want to hard-code it).

If the range you want to populate the list is located on another worksheet, you can't refer to it using the standard =sheet2!a1:a4 style. You'll have to create a named range, and refer to it by that name.

BradC
  • 39,306
  • 13
  • 73
  • 89
  • 1
    Yes, you may refer to another worksheet using named ranges as explained in http://office.microsoft.com/en-us/excel-help/create-a-drop-down-list-from-a-range-of-cells-HP005202215.aspx (the explanation is collapsed) – Dr. belisarius Nov 10 '10 at 14:39
  • Glad to help. I suggest to add that to your answer, so future readers don't need to scan through comments. – Dr. belisarius Nov 10 '10 at 18:11
  • I'm using this in LibreOffice Calc, and it works pretty much the same way, except that the menu item is Data / Validity, and the options and syntax are a bit different. Thanks! – Randy Orrison Nov 10 '10 at 22:08
  • I tested this in Excel 2008 for Mac, and it works if the named range is in the same sheet, but if you try to refer to a named range in another sheet, it doesn't work. Can anybody confirm which versions of Excel support this feature? – PaulJ Dec 15 '14 at 18:18
2

Why not use a dropdown?

http://office.microsoft.com/en-us/excel-help/create-a-drop-down-list-from-a-range-of-cells-HP005202215.aspx

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • The en-us page link is broken but this equivalent link is still working, http://office.microsoft.com/en-in/excel-help/create-or-remove-a-drop-down-list-HP005202215.aspx – JohnC Dec 20 '14 at 22:14
  • 1
    That link is broken too. Data Validation (See answer from @BradC) can show a dropdown. – Dizzley Mar 01 '15 at 06:34
  • 2
    New link: https://support.office.com/en-us/article/Create-or-remove-a-drop-down-list-5a598f31-68f9-4db7-b65e-58bb342132f7?CorrelationId=2154979d-a8d8-4a4e-9b12-c465ddd61237&ui=en-US&rs=en-US&ad=US – Fionnuala Mar 01 '15 at 11:13