 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008Display from dropdown
When creating a dropdown list, how can I have an item appear for choice but output from choice an abbreviation? Example, I have a department list in namedrange DEPT. ACTG (Accounting) PURC (Purchasing) INVT (Inventory) I want the user to see Accounting, Purchasing, Inventory, but the output from their choice to be the appreviated version (ACTG, PURC, INVT). I don't want to display the entire thing to the user only the description. Any way to do this? There is a sample file that demonstrates this: http://contextures.com/excelfiles.html#DataVal Look for: DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code.  SignatureBiff Microsoft Excel MVP > When creating a dropdown list, how can I have an item appear for choice > but [quoted text clipped - 8 lines] > I don't want to display the entire thing to the user only the description. > Any way to do this? Thank you thank you T.! > There is a sample file that demonstrates this: > [quoted text clipped - 17 lines] > > I don't want to display the entire thing to the user only the description. > > Any way to do this? This sample show the return values as numeric and uses 'If Target.Cells.Count > 1' How can I adapt to return my abbreviations? My sheet with the list is 'ChoiceLists', my named range is 'DEPT'. My sheet where choice is made is 'Projects'. Where is the code that would point to the abbreviation that goes with the choice? So far the user is seeing the full choices and it is returning the same full choice, not the abbreviations which is in the column in front of the full description. I added the code Here is my code adapted, but of course there is no count going on. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then GoTo exitHandler If Target.Column = 2 Then If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Target.Value = Worksheets("ChoiceLists").Range("C1") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("ChoiceLists").Range("DEPT"), 0), 0) End If exitHandler: Application.EnableEvents = True Exit Sub End Sub > There is a sample file that demonstrates this: > [quoted text clipped - 17 lines] > > I don't want to display the entire thing to the user only the description. > > Any way to do this? >My sheet where choice is made is 'Projects'. Tell me *exactly* where your drop down cell is. Is there just a single drop down? SignatureBiff Microsoft Excel MVP > This sample show the return values as numeric and uses 'If > Target.Cells.Count [quoted text clipped - 53 lines] >> > description. >> > Any way to do this? Sheet 'Projects', starting cell C2 on down Example cell C1 says 'DEPT' Cell C2,C3,C4 etc have the dropdown. User click on cell C2, sees and clicks on dropdown, the descriptive list shows (Accounting, Purchasing, etc). The user clicks a choice from the dropdown list (say, Accounting), right now the cell C2 get filled with 'Accounting'. I want the abbreviation to come back instead. ie user sees and clicks on 'Accounting' but 'ACTG' is returned. My actual list - namedrange 'DEPT' is on sheet 'ChoiceLists'. Cell C1 says DEPT, Cell D3 says 'Department' The abbreviations are in cells C3-C10 (or so), the descriptions are in cells D3-D10. > >My sheet where choice is made is 'Projects'. > [quoted text clipped - 58 lines] > >> > description. > >> > Any way to do this? Ok, your description is a little confusing! Let's assume the drop down lists are in the range Projects!C2:C5 The departments are in the named range Depts on sheet ChoiceLists!D3:D5 The department codes (abbreviations) are in the range ChoiceLists!C3:C5 Navigate to sheet Projects Right click on the sheet tab and select View code Copy/paste the code below into the window that opens Modify this line of the code to reflect your actual range size where the drop down lists are located: If Not Intersect(Target, Me.Range("C2:C5")) Is Nothing Then If the actual range is C2:C25, then change to: If Not Intersect(Target, Me.Range("C2:C25")) Is Nothing Then 'Start of code----------- Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then GoTo exitHandler If Not Intersect(Target, Me.Range("C2:C5")) Is Nothing Then If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Target.Value = Worksheets("ChoiceLists").Range("D3") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("ChoiceLists").Range _ ("Dept"), 0) - 1, -1) End If exitHandler: Application.EnableEvents = True Exit Sub End Sub SignatureBiff Microsoft Excel MVP > Sheet 'Projects', starting cell C2 on down > Example cell C1 says 'DEPT' [quoted text clipped - 81 lines] >> >> > description. >> >> > Any way to do this? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.