Excel query

  • Thread starter Thread starter Judy Scott
  • Start date Start date
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
 
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
 
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
 
Back
Top