drop down selections

[gavmer]

Hi all,

I have a problem. I have approximately 100 combo boxes to select item
that are linked to various cells. Each drop down holds 4 items. When
use the boxes to select items and save the book afterwards, th
information in the linked cells is ok BUT, the item displayed in th
combo box may not necessarily be the one that was selected. I woul
only like this to remain so others can see what was selected in th
combo box.

Any ideas???

Cheers!!!

 

[Arvi Laanemets]

Hi

Why don't you use Data Validation lists instead?

 

[Guest]

Hi Arvi,

Situation is that each of the 100 approx items has up to 3 options to choose. pricing on each of the 3 options varies for the 100 items. IE: the 100 items are products that have options that can be added but the pricing is different for each.
With validation, my user would need to see the name of the option, select it but have the price insert into the cell and not the item name. This would need 2 columns, would it not??

Hi

Why don't you use Data Validation lists instead?

 

[Debra Dalgleish]

There's a sample here:

http://www.contextures.com/excelfiles.html#DataVal

under the heading 'Data Validation Change', that uses an event procedure
to change the product name to a product code. You could adapt that to
insert the price if an option is selected.

Hi Arvi,

Situation is that each of the 100 approx items has up to 3 options to choose. pricing on each of the 3 options varies for the 100 items. IE: the 100 items are products that have options that can be added but the pricing is different for each.
With validation, my user would need to see the name of the option, select it but have the price insert into the cell and not the item name. This would need 2 columns, would it not??

:

 

[Arvi Laanemets]

Hi

OK. Let's clear it out. From your posting I understand, that:

You sell ~100 various items, with base price for every one of them set.
Every item can have 0 - 3 additional options - every option has a price (are
there 100 sets of option prices - for every item - or just 3 ?).
You need the summary price to be calculated, i.e.
SummaryPrice=BasePrice+(Option1<>"")*Option1Price+(Option2<>"")*Option2Price
+(Option3<>"")*Option3Price

I'm right or not? When yes, then a design I would use (options vary for
every item).

Create a sheet Prices with header in row 1.
Prices: Item, BasePrice, Option1, Option2, Option3, Opt1Price, Opt2Price,
Opt3Price
and fill the table.

Create named ranges
Items=OFFSET(Prices!$A$2,,,COUNTIF(Prices!$A:$A,"<>")-1,1)
BasePrices=OFFSET(Prices!$A$2,,,COUNTIF(Prices!$A:$A,"<>")-1,2)

Create a sheet p.e. Main with header in row1
Main: Item, Option1, Option2, Option3, Price

Select Main!A2 and create a named ranges
ItemIndex=MATCH(Main!$A2,Items)
Options=OFFSET(Prices!$C$1,ItemIndex,,1,COUNTIF(OFFSET(Prices!$C$1,ItemIndex
,,1,3),"<>"))
OptionPrices=OFFSET(Prices!$C$1,ItemIndex,3,1,COUNTIF(OFFSET(Prices!$C$1;Ite
mIndex,,1,3),"<>"))
BasePrice=IF(ISERROR(VLOOKUP(Main!$A2,BasePrices,2,0)),"",VLOOKUP(Main!$A2,B
asePrices,2,0))

Select Main!B2 and create named range
Option1Price=IF(ISERROR(INDEX(OptionsPrices,1,MATCH(Main!$B2,Options))),"",I
NDEX(OptionsPrices,1,MATCH(Main!$B2,Options)))
Select Main!C2 and create named range
Option1Price=IF(ISERROR(INDEX(OptionsPrices,1,MATCH(Main!$C2,Options))),"",I
NDEX(OptionsPrices,1,MATCH(Main!$C2,Options)))
Select Main!D2 and create named range
Option1Price=IF(ISERROR(INDEX(OptionsPrices,1,MATCH(Main!$D2,Options))),"",I
NDEX(OptionsPrices,1,MATCH(Main!$D2,Options)))

Format Main!A2 as data validation list with source
=Items
Format Main!B23 as data validation list with source
=Options
Into Main!E2 enter the formula
=IF($A2="","",BasePrice+IF(Option1Price="",0,Option1Price)+IF(Option2Price="
",0,Option2Price)+IF(Option3Price="",0,Option3Price))

Select the range Main!A2:E2, and copy it down as much as you need. It's
done!

NB! Don't let you be confused with order of options on pricelist, and
options selected on main sheet. On main sheet you can select options for an
item in any order. Sorry, but I couldn't elliminate the possibility, that an
option is selected twice for same item - you cant use validation twice on
same cell. And with current design there must be always at least one option,
otherwise the named range returns an error. But it doesnt affect formulas on
Main sheet, so let it be.


--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)

Hi Arvi,

Situation is that each of the 100 approx items has up to 3 options to

choose. pricing on each of the 3 options varies for the 100 items. IE: the
100 items are products that have options that can be added but the pricing
is different for each.

With validation, my user would need to see the name of the option, select

it but have the price insert into the cell and not the item name. This would
need 2 columns, would it not??

 

[Arvi Laanemets]

A correcture (I missed a semicolon when replacing them with commas in
formulas):
OptionPrices=OFFSET(Prices!$C$1,ItemIndex,3,1,COUNTIF(OFFSET(Prices!$C$1,Ite
mIndex,,1,3),"<>"))


Arvi Laanemets