0

I've created a dropdown list of different type's of lamp fittings, I created a 2nd dropdown list with different lamp wattages. I want to setup my Excel dashboard so that if you select a lamp fitting from the dropdown list the 2nd column will populate the corresponding wattage or only allow you to select the correct wattage.

An example is this, say you select 1-Lamp 2' T8 18W from column A named lamp fixtures then column B named Wattage will only show 18W when you select that drop down list.

I tried setting up a dependent dropdown list but this doesn't seem to work.

I have around 80 different lamp fixtures with 80 corresponding wattages.

How can I set this up?

Improve this question
2
  • Is there only one possible wattage for each fixture?
    – Werrf
    Jul 13, 2017 at 12:48
  • Yes, there is. I am developing a lighting calculator so the first spreadsheet has all the user inputs and the user will select the appropiate fixture in column A then Column B will populate the correct wattage. Then the user will move down to the next row and select another fixture or the same fixture from the dropdown menu then Column B will populate with the correct wattage and so on.
    – Damon
    Jul 14, 2017 at 13:55

1 Answer 1

Reset to default
0

If each lamp has only one wattage, then you don't really need a dropdown box for the wattage. You could just have a cell display the appropriate wattage as soon as a lamp is selected in the lamp dropdown.

[EDIT: To provide more detail and clarification based on your comment below.]

You can use VLOOKUP() to show the wattage. The formula will look like this:

=VLOOKUP(lamp,datarange,2,FALSE)

Here lamp is derived from the output of the lamp dropdown box, datarange is an array holding your lamp names in the first column and their wattages in the second column. The first column could also be used as the array that specifies your dropdown list.

Here's an example of how to do this:

enter image description here

The output of the dropdown (combo) box is just the position of the selected lamp in the list. That number is displayed in a cell that you specify (here it is A13). The number in A13 will change whenever a different lamp is selected in the dropdown box. To get the actual name of the lamp, we need to use the INDEX() function as shown below. The formula in D6 is:

=VLOOKUP(INDEX($A$2:$A$11,A13),$A$2:$B$11,2,FALSE)

Here INDEX() gets the lamp name (the 7th item in A2:A11), and VLOOKUP() looks it up in column 1 of A2:B11, and returns the value in column 2.

From your comment, it looks like you'll need several combo boxes. They can all use the same lookup table, which can be located on another sheet or hidden, if necessary, for appearance's sake.

Setup a combo box by CTRL-clicking on it and choosing "Format Control..." In the resulting dialog box, you specify the array holding the list of items to be displayed and the cell that will hold the output, called the "Cell link".

Improve this answer
3
  • Not sure this will work, I am developing a lighting calculator so the first spreadsheet has all the user inputs and the user will select the appropiate fixture in column A then Column B will populate the correct wattage. Then the user will move down to the next row and select another fixture or the same fixture from the dropdown menu then Column B will populate with the correct wattage and so on.
    – Damon
    Jul 14, 2017 at 14:06
  • OK, I have tried this by selecting all the lamp fixtures in column A and then selecting Column A and B as the table array along with the column index being 2 and FALSE but I don't get the right figure populating in the 2nd column and when I select other fittings from the dropdown the results don't change.
    – Damon
    Jul 14, 2017 at 14:28
  • @Damon, have you gotten your dropdown to work correctly? Updated explanation is shown above.
    – Bandersnatch
    Jul 17, 2017 at 15:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .