Is there a way to change what is entered into a cell from a drop down list..
Basiclly what I want to do is when the user selects a state from the drop
down list lets say from column a1 I want instead of the state name to fill in
the cell, I want the state abbreviation to fill the cell again, based off the
users selection from the drop down list. I created the drop down list useing
the Data Validation tool. I thought of using an If statement however that
would be monsterous due to there of course being 50 states. Any assistance
would be greatly appreciated.

Thank you

On Oct 4, 1:58 pm, Mekinnik
wrote:
Is there a way to change what is entered into a cell from a drop down list..
Basiclly what I want to do is when the user selects a state from the drop
down list lets say from column a1 I want instead of the state name to fill in
the cell, I want the state abbreviation to fill the cell again, based off the
users selection from the drop down list. I created the drop down list useing
the Data Validation tool. I thought of using an If statement however that
would be monsterous due to there of course being 50 states. Any assistance
would be greatly appreciated.

Thank you

Hi Mekinnik,

Does the abbreviation have to be in the same cell? I'm not sure that's
possible as the validation would only allow the full state names ??

In another cell you could have a VLOOKUP formula though.

Next to the list you use for your data validation add a column with
corresponding abbreviations and reference this from your VLOOKUP
formula. Of course this will give you a state name and an
abbreviation, but you could hide the column with the full names in it
before sending out your report.

Alternatively you could use a listbox or combobox instead of data
validation. Depending on your sheet this could complicate things but
would allow you to have just one cell with the abbreviation in it (the
state name will be in the box). You can make the box so that it's not
printed so although you'll see it in your sheet you won't on your
printed report.

HTH

cheers,
t.

I beieleve I have done what you have suggested. I have a worksheet named
"Lists". In this worksheet I have 2 columns populated, column A has the state
names and column B has the state abbreviation. I am not sure how to write the
vlookup formula. On another worksheet I have the dropdown list, now if I
understand you correctly I can reference column B and make the abbreviation
be entered into the cell not the statename? I will just need a little help
with this.

"troy@eXL" wrote:

On Oct 4, 1:58 pm, Mekinnik
wrote:
Is there a way to change what is entered into a cell from a drop down list..
Basiclly what I want to do is when the user selects a state from the drop
down list lets say from column a1 I want instead of the state name to fill in
the cell, I want the state abbreviation to fill the cell again, based off the
users selection from the drop down list. I created the drop down list useing
the Data Validation tool. I thought of using an If statement however that
would be monsterous due to there of course being 50 states. Any assistance
would be greatly appreciated.

Thank you

Hi Mekinnik,

Does the abbreviation have to be in the same cell? I'm not sure that's
possible as the validation would only allow the full state names ??

In another cell you could have a VLOOKUP formula though.

Next to the list you use for your data validation add a column with
corresponding abbreviations and reference this from your VLOOKUP
formula. Of course this will give you a state name and an
abbreviation, but you could hide the column with the full names in it
before sending out your report.

Alternatively you could use a listbox or combobox instead of data
validation. Depending on your sheet this could complicate things but
would allow you to have just one cell with the abbreviation in it (the
state name will be in the box). You can make the box so that it's not
printed so although you'll see it in your sheet you won't on your
printed report.

HTH

cheers,
t.

www.eXtreme-eXcel.com
....be indispensable... you'll earn twice as much!

There's a sample file here that fills in a product code, after a product
is selected. You could adapt the technique to your workbook:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0004 - Data Validation Change'

Mekinnik wrote:
Is there a way to change what is entered into a cell from a drop down list..
Basiclly what I want to do is when the user selects a state from the drop
down list lets say from column a1 I want instead of the state name to fill in
the cell, I want the state abbreviation to fill the cell again, based off the
users selection from the drop down list. I created the drop down list useing
the Data Validation tool. I thought of using an If statement however that
would be monsterous due to there of course being 50 states. Any assistance
would be greatly appreciated.

Thank you


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

On Oct 5, 8:36 am, Debra Dalgleish wrote:
There's a sample file here that fills in a product code, after a product
is selected. You could adapt the technique to your workbook:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0004 - Data Validation Change'

Mekinnik wrote:
Is there a way to change what is entered into a cell from a drop down list..
Basiclly what I want to do is when the user selects a state from the drop
down list lets say from column a1 I want instead of the state name to fill in
the cell, I want the state abbreviation to fill the cell again, based off the
users selection from the drop down list. I created the drop down list useing
the Data Validation tool. I thought of using an If statement however that
would be monsterous due to there of course being 50 states. Any assistance
would be greatly appreciated.

Thank you

--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html

Hi Mekinnik,

Sorry I've been offline for a couple of weeks. You're probably all
fixed up now but if you'd still like help with the vlookup try this:

Assuming the data validation cell where you select the state name is
B5...
=VLOOKUP(B5,Lists!$A$1:$B$52,2,FALSE)
Remember this needs to be in a different cell to the data validation
one. When you select the state in cell B5, this cell with the VLOOKUP
formula (eg C5) will automatically be updated.

HTH. If not and you're still stuck, let me know.

Cheers mate,
Troy.

Unprotect Any Spreadsheet... Without The Password... In Just Seconds
Get eXL_unProtect today for less than you'd pay for lunch!
www.eXtreme-eXcel.com
Don't Let Anyone Lock You Out Of A Spreadsheet Again!