Automatically update list box items

[Oriana]

I'm trying to create a list box that has two values that
can change often. I tried a list box ActiveX object, but
that didn't work, because the source values are in a row,
not in a column. I tried Data -> Validation, and this
seems to work okay, but I can't get the cell to refresh
each time the source cells are updated. Any ideas?

 

[Tom Ogilvy]

Is calculation set to automatic. The normal behavior with automatic is for
the list to update on each calculate which there should be one if the cells
are updated.

 

[Oriana]

Yes, calculation is automatic. I should rephrase the
original question -- The list does get updated, but the
cell with the list's selection doesn't. Basically, A1 has
data validation set to be from the list B1:C1. Say I
select B1's value for A1. If B1 then changes, the value
in A1 doesn't.

(Hope this makes sense!)

Thanks,
Oriana.

 

[Tom Ogilvy]

I was actually afraid that is what you meant.

Data validation on applies when you make the entry. Once the value is in
the cell, it is not affected by data=>Validation. You would need a
combobox/dropdown box from the forms toolbar to do what you want. Changing
the selected value in the source changes it in the combobox. The drawback
is when you link that to a cell, it displays the index of the selection.
With this type, you usually link to the cell underneath the combobox and
then use a formula such as

=index(listfillrange,linked cell Value,1)

Regards,
Tom Ogilvy

 

[Oriana]

Thanks Tom,

I was afraid that this would be the answer! I've actually
worked around it using a drop down with the
enteries "List" and "Net". In my original cell, I then do
conditional processing based on this value [if
(A1="list",formula1,formula2)], and it seems to work fine.

Thanks you very much for your help.
-Oriana.