Drop-down list determining output data

Question

Hello,

I have multiple pricing structures (12) and created a drop-down list showing each of the pricing structures (MSPR, Distributor Qty. 1-9, Distributor 10-19, Distributor 20-29, etc.). How do I make that pricing structure appear in that column based on what is chosen in the drop down list? Vlookup?

Answer 1

Hi Alex,

You can use a combination of Data Validation and INDIRECT function to create a dependent drop-down list. Start with creating a list of each category. See the sample image below.

Place a unique Define Name on each category by following these steps:

1. Highlight the list and right-click it.
2. Choose Define Name.
3. Enter the unique name of the category.
4. Hit OK.

Once you’re done with creating the list, you can start with applying the Data Validation in by following the instructions in this article. On the column where the price should be, apply Data Validation with INDIRECT function by following the steps below:

1. Select the first cell.
2. Go to the Data tab.
3. Click Data Validation.
4. Choose Data Validation.
5. Select List on the Allow section.
6. On the text box under Source, enter =INDIRECT(cell number). See this image.

Apply the formula to the other cells on the Price column.

If you have other questions, feel free to post back.