Hi,
Can some body help me
I want to make a Data validation list that gives a list of countries,
is it possibal to get the value (country) to change to the shortening in the same cell.
For excample, in cell A1 i choose country Sweden, so in sted of writing sweeden in cell A1, excel gives the answer SWE.
Why can't you change the list to show Sweden as SWE ???
Ash
because i have over 50 different countries and several people using the document.
So i when you choose a country it gives the shortening as answer,
if one guy shortens swe as something else then i get a problem with the following function
can you share your file please.............remove any sensitive data
Do you always want it shortened to 3 characters, or could it be variable?
Here is the problem in a small scale
In the table in column A i want a list with countries, from A14-A19
But instead of giving the complete name i want to give the short version from cells B14-B19
Then in column B i use index and match to look which continent the country is located in.
As an excample
In sell A6 i choose from the list russia
but the anser in cell A6 should bee RUS not russia
it can be variable,
i got a list of countries and next to them the shortening
so if it would be possible to get data validation list in one cell and a index match in the same cell to find the shortening, that would be great
Does adding a column work for you?
Please check
See if this helps.
A bit of visual deception using a little bit of vba (macro) code and a hidden column.
exactly like that jason.b75
but when you open the list of countries its hard to reed because the list is very narrow can you widen it some how
Oops.
I forgot to check it after hiding the column.
It can only be made wider by unhiding the hidden column, which would defeat the purpose.
Common method would be to overlay the cells with comboboxes, but I'm going to try some alternatives and see which one works.
Instead of hiding the column, the cells could be locked and the text formatted white - would that work?Originally Posted by jason.b75
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
but if we switch the hidden column on the left side and the column with the shortening to the right would it work
By the way can you tell how the VBA code works, i like to learn more about VBA
the formula works if you flip the rows
But how do i duplicate the formula so that it does the same thing in a other column,
I have tried to change the name and SelectionChange and target but it doesn't work
That could work, I was trying a different approach to keep it in the same column.
Does this work for you?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks