' - BEGIN For applicable =columns, pull in ID for description selected from drop-down lists.ĭropDownListRangeName = Col5HeaderDropDownRangeNameĭropDownListRangeName = Col9HeaderDropDownRangeName Private Const Col9HeaderDropDownRangeName as String = "col9HeaderDropDownRangeName" Private Const Col5HeaderDropDownRangeName as String = "col5HeaderDropDownRangeName" ' - Columns' Drop-Down Lists' Range Names Private Const Col9HeaderColumnNumber As Integer = 9 Private Const Col5HeaderColumnNumber As Integer = 5 NOTE: In practice, e.g., "Col5Header" would be, e.g., "ProductID", and "Col9Header" would be, e.g., "SalesID". ' - NOTE: Declare "EmptyString" in a global declarations Module. The only things different between the two copy-pasted code blocks are the column #s and the range names, so as much as possible, everything else should not be duplicated, so, instead, the code should, e.g., be: Tony: Correct, but next step is to avoid encouraging: a) violations of the DRY Principle (and therefore increased: a.1) chances of errors and costs to fix them and a.2) costs of potential future changes / enhancements) by not encouraging "Copy-Paste" programming by showing how it can be "refactored" to reduce duplicate code, and b) hard-coded literal ("magic" "number" / "string") constants by declaring and using Named Constants that are much harder to mistype without triggering a compiler error. □ User-Friendly: A breeze for beginners and a powerful tool for experts.ĭownload Now and Soar Through Time with Excel! □ Seven Types of Drop-down Lists: Make data entry a breeze with drop-down lists of various features and types. □️ Powerful Navigation Pane: Gain an advantage with the robust Column Manager, Worksheet Manager, and Custom Favorites. □ Customizable Charts and Reports: Access a broad variety of additional charts and generate insightful reports that tell a story. ⏱️ Efficient Batch Operations: Why put in extra effort when you can work smart? Import, export, combine, and tweak data in bulk with ease. □ Superior Data Processing: Merge cells, remove duplicates, and perform advanced data conversions – all without breaking a sweat! □️ Over 300 Powerful Features: Kutools is packed with more than 300 advanced features, simplifying your work in over 1500 scenarios. Streamline intricate tasks and glide through your data like a pro. Ready to supercharge your Excel tasks? Harness the power of Kutools for Excel - your ultimate time-saving tool. Transform Hours into Minutes with Kutools for Excel! Then save and close this code, now, when you select an item from the drop down list, a relative different value is displayed in the same cell, see screenshot:ĭemo: Create drop down list but show different values in Excel Note: In the above code, the number 5 within If Target.Column = 5 Then script is the column number that your drop down list located,, the “ dropdown” in this selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False) code is the range name you have created in step 1. SelectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False) VBA code: Display different value from the drop down list: Private Sub Worksheet_Change(ByVal Target As Range) After inserting the drop down list, please right click active sheet tab, and select View Code from the context menu, and in the opened Microsoft Visual Basic for applications window, copy and paste the following code into the blank Module: In the Data Validation dialog box, under the Settings tab, choose List from the Allow drop down, and then click button to select the Name list which you want to use as drop down values in the Source text box. Then select cells where you want to insert the drop down list, and click Data > Data Validation > Data Validation, see screenshot:ģ. Create a range name for the cell values you want to use in the drop down list, in this example, I will enter the name dropdown in the Name Box, and then press Enter key, see screenshot:Ģ. To finish this task, please do with the following step by step:ġ. This article will introduce the details to solve this task.Ĭreate drop down list but show different value in drop down list cell In Excel worksheet, we can quickly create a drop down list with the Data Validation feature, but, have you ever tried to show a different value when you click the drop down list? For example, I have the following two column data in Column A and Column B, now, I need to create a drop down list with the values in Name column, but, when I select the name from the created drop down list, the corresponding value in Number column is displayed as following screenshot shown. How to create drop down list but show different values in Excel?
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |