Create dropdown list in excel that displays different values from the same lookup table

0 votes

In Excel, I'm attempting to make a drop-down list where, depending on which option is selected, the text will show a different value in relation to a table on another sheet. In my case, I want the choice to display a more widely used site name before switching to displaying the StationID. Although I tried the following VB code, I was unable to make it work for my example. All I can think of that might be causing a problem is the fact that these are in the same workbook but on different worksheets. Even after changing the code and attempting to establish a variable for the lookup table on a different worksheet, nothing changed.

Here is my Code :

Private Sub Worksheet_Change(ByVal Target As Range)
'Created By Excel 10 Tutorial
    siteName = Target.Value
    defRange = ActiveWorkbook.Sheets(“LookUpTable”).Range(“WISKILookupTable”)
    If Target.Column = 1 Then
        StationNum = Application.VLookup(siteName, defRange, 2, False)
        If Not IsError(StationNum) Then
            Target.Value = StationNum
        End If
    End If
End Sub
Apr 6, 2023 in Others by Kithuzzz
• 38,010 points
281 views

1 answer to this question.

0 votes

Looks like the Worksheet_Change() sub needs two edits:

Dim defRange As Range   ' Without this statement, defRange is a variant type, not a range

And add a Set statement to defRange =:

Set defRange = ActiveWorkbook.Sheets(“LookUpTable”).Range(“WISKILookupTable”)

Hence, the defRange variable now refers to the actual range. If not, the values in that range are used for defRange, which is not what you want.

Also, it's generally a good idea to include an Option Explicit declaration on the module's initial line. That will alert Excel to problems involving variable types and will detect the Set statement that is missing.

You will be forced to Dim every variable by Option Explicit, but that's also best practice.

answered Apr 6, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,420 points
535 views
0 votes
1 answer

How to trick an Excel function that wants a column as input to accept a list of values as if these were in a column

Use VSTACK: vstack to make an array: Use it as value ...READ MORE

answered Mar 18, 2023 in Others by narikkadan
• 63,420 points
249 views
0 votes
1 answer
0 votes
1 answer

Excel stock and sales data management

you must attach the event handler each ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,420 points
297 views
0 votes
1 answer

Crawling through multiple excel files, match and copy data to master file

One application only. It would be quicker ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,420 points
332 views
0 votes
1 answer

Paste a value from a drop-down list

Check if there is an existing sheet ...READ MORE

answered Jan 14, 2023 in Others by narikkadan
• 63,420 points
330 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
836 views
0 votes
1 answer

How to create Dropdown list in excel using php

Try this: $objValidation = $objPHPExcel->getActiveSheet()->getCell('B'.$i)->getDataValidation(); $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST ); $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,679 views
0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,420 points
1,100 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP