How to limit cell value in Excel to options from another sheet?

10
votes

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.

2
excelexcel-2003validation

2 Answers

14
votes

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.