Excel query

J

Judy Scott

Hello

I am trying to add a drop-down list to a cell with info
like different brands of smoke detectors and the
corresponding prices. When I select a brand the list
disappears and leaves me with the brand and price in that
cell.

Is this possible?

Judy
 
A

Arvi Laanemets

Hi

On empty sheet, enter the table (starting from A1)
Brand1 Price1
Brand2 Price2
....

Let's assume your table fills the range A1:B20
Rename the sheet p.e. as "PriceTable"
Select from menu Insert.Name.Define
Into name field enter some name, p.e. PriceList
Into source field, enter the formula:
=OFFSET($A$1,,,COUNTIF($A:$A,"<>"),0)
Press OK
Define another name, p.e. PriceTable, with formula in source field:
=OFFSET($A$1,,,COUNTIF($A:$A,"<>"),1)
Hide the sheet PriceTable (right-click onto sheet tab and select hide), when
you don't want it to be visible

Select the sheet you want to ad the drop-down into
Select the cell you want to add drop-down into (let's saaume it's A2)
Select from menu Data.Validation.List
Into source field enter:
=PriceList
Select the cell next to drop-down, and enter the formula:
=VLOOKUP(A2,PriceTable,2,FALSE)

Now, when you select a brand in A2, the according price is displayed in B2.
And you can add new items into PriceTable, or remove them, or rename/reprice
an item, or/and sort the PriceTable as you want, with all changes appeaering
on your working sheet immediately, without any need to edit the formulas.


Arvi Laanemets
 
A

Arvi Laanemets

Sorry, named range's sources must be:
=OFFSET(PriceTable!$A$1,,,COUNTIF(PriceTable!$A:$A,"<>"),0)
=OFFSET(PriceTable!$A$1,,,COUNTIF(PriceTable!$A:$A,"<>"),1)


Arvi Laanemets
 
Top