create a list box in one cell of state abbreviations
 

How do I create a list box within a cell of state abbreviations which i can
then use as a lookup reference for a formula applying tax rates.

create a list box in one cell of state abbreviations
 

"spitcher" wrote
How do I create a list box within a cell
of state abbreviations which i can
then use as a lookup reference for a formula applying tax rates.

A data validation (DV) droplist
would also do the job nicely

Try this little experiment

In Sheet1,

List the state abbrevs in A1 down, eg:
AAA
BBB
CCC
etc

Now we'll create a dynamic range for the list,
which can then be referred to / used in
any other sheet within the book

Click Insert Name Define
and insert, under:
Names in workbook: StateAbbrv
Refers to:
=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A))
Click OK

In say, Sheet2,
Suppose we want the DV droplist to be within A2:A10

Select A2:A10
Click Data Validation
and make the settings:
Allow: List
Source: =StateAbbrv
Click OK

Test it out .. When we click on say, A3,
we'd get the droplist to select the state abbrv

(using a dynamic range allows us to easily maintain
the DV list via editing the source in Sheet1)

For an intro and more on data validation,
see Debra's comprehensive coverage at her:
http://www.contextures.com/xlDataVal01.html
(as a start, there's other pages on DV)

--------

For a listbox play ..
(assuming we've set up the dynamic range above)

In say, Sheet3

Draw a listbox from the Forms toolbar
Right-click Format Control
and insert under:
Input range: StateAbbrv
Cell link: $B$1 (say)

You should see the list box populated
with the items from StateAbbrv

Clicking on / selecting the item in the listbox
will put its corresponding number into the cell link, B1
(eg, selecting CCC will place: 3 into B1)

And if we want to retrieve the state abbrevs itself,
we could put in say, C1: =INDEX(StateAbbrv,B1)

Alternatively, a more direct way
to put the listbox selection into the linked cell itself
would be to use a listbox from the Control toolbox

Draw a listbox from the Control toolbox
Right-click Properties
and then key-in for:
ListFillRange: StateAbbrv
LinkedCell: E1 (say)

Then uncheck the triangle icon to exit design mode

Test it out, selecting CCC in the listbox
would put: CCC into the linked cell E1, and so on

Activate the Forms / Control toolbars if necessary
via clicking: View Toolbars
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---