How to use two dependent combo boxes to display prices?

**tomkilbourn** · 01-21-2011, 06:12 AM

Hello, I am trying to set up a spreadsheet showing photocopying prices.

There are two categories of prices of paper - Shops own paper, and Customers own paper.

I have two comboboxes, the first lets me select the category of paper, and then I want the second to display the corresponding prices for the selected category from the first combobox.

I have used combo boxes with the Link Cell Lookup function to display prices from the sheet, but I'd love to be able to choose a category from the first combobox then display the prices for that category in the second combobox.

Can it be done, and can I keep the lookup and link cell functions?

**outofthehat** · 01-21-2011, 08:49 AM

Here's a very simple way to do what you want using data validation on cells (i.e. you don't need controls). In 2003 it's on the Data menu, Data > Validation.
http://helpdeskgeek.com/office-tips/...ists-in-excel/

**tomkilbourn** · 01-21-2011, 11:10 AM

Cool, thankyou. So I dont need to use the Lookup and Link Cell functions then?

**outofthehat** · 01-24-2011, 04:13 AM

I''ve only just looked at Sheet1 !

I'm now not sure exactly what you're trying to achieve. Looking at Sheet1, it's not clear why you want a second drop-down box? You already have a box in which the customer chooses their option, then the correct price is put in column G, so why have a second drop-down?

Incidentally, rather than hard-coding the ranges in column G, I'd use a lookup on the data shown on the page (or better still, on a master prices sheet so you can maintain it).
e.g. on F44 put data validation, to allow 'None', 'Own Paper', 'Agency Paper', then use HLOOKUP to extract the corresponding value in the price table in B43:C45.

As a general point, I'd be inclined to have a master prices table on a different sheet, where it's easy to maintain, and feed the data through to your forms from there.

And as another aside, I'd strongly advise against merging cells anywhere where you're writing formulae, e.g. the data tables on Sheet2, because it makes use of formulae much harder and less reliable (e.g. the merged cells throw off data validation lists).