Data Validation linked to cell Formula

I have a spreadsheet where i have created a number of drop down lists using
data validation. These lists refer to cells which in turn refer to other
cells. I some of the Drop downs I created the cells update due to changes in
data elsewhere in the sheet, however in others they don't., I wouls like them
all to update.

I cant see what I doing wrong any assistance great fully recieved.

If you give an example of the layout, and the formulas that you're using
in the Data Validation cells, someone may be able to help.

Buzz wrote:
> I have a spreadsheet where i have created a number of drop down lists using
> data validation. These lists refer to cells which in turn refer to other
> cells. I some of the Drop downs I created the cells update due to changes in
> data elsewhere in the sheet, however in others they don't., I wouls like them
> all to update.
>
> I cant see what I doing wrong any assistance great fully recieved.

Sorry new to this discussion group thing , I did not want to overburden with
detail intially, but below is more detail

cell A1 has a drop down which references cells b1:b3, cell b1 contains a
formula "=c1", in some of the drop downs when c1 changes the drop down menu
automatically updates, and is some its only when you select the drop down
menu do you see the correct values in the drop down menu. I would like all
the drop downs to automaticaaly update.

In the drop downs which work in a1 you can see the formula "=c1", in the
others you only see the value of C1 any assistance greatly appreciated.


"Debra Dalgleish" wrote:

> If you give an example of the layout, and the formulas that you're using
> in the Data Validation cells, someone may be able to help.
>
> Buzz wrote:
> > I have a spreadsheet where i have created a number of drop down lists using
> > data validation. These lists refer to cells which in turn refer to other
> > cells. I some of the Drop downs I created the cells update due to changes in
> > data elsewhere in the sheet, however in others they don't., I wouls like them
> > all to update.
> >
> > I cant see what I doing wrong any assistance great fully recieved.
>
>

I don't think that there's anything inside Data|Validation that will change the
value of a cell when the list changes.

In fact, lots of lists can are based on dynamic ranges--that grow and contract
when the number of items in the list are added/deleted. It would be pretty
difficult to make sure you cover every contigency.

And even more...You can use data|validation as a warning tool. You can let the
user type something in that cell with data|validation that doesn't appear on the
list.

It could be quite a pain to come up with something that would work all the time
(although, maybe under specific circumstancs, you could have something that
worked fine).

But it might be easier to drop the data|validation and use a combobox from the
Control toolbox toolbar.

Put it over cell A1.

While in design mode (another icon on that control toolbox toolbar), right click
on it and choose properties.

Set the style to fmStyleDropDownList (to only allow choosing from the dropdown)

Assign the linked cell to A1 (so you can use it other formulas???)

Assign the ListFillRange to B1:B3.

Now if the combobox has the value equal to what's in B1 (which points at C1),
and C1 changes, the value in the combobox (and A1) will change.

This seems a lot simpler than struggling with Data|Validation--well, to me
anyway.

Buzz wrote:
>
> Sorry new to this discussion group thing , I did not want to overburden with
> detail intially, but below is more detail
>
> cell A1 has a drop down which references cells b1:b3, cell b1 contains a
> formula "=c1", in some of the drop downs when c1 changes the drop down menu
> automatically updates, and is some its only when you select the drop down
> menu do you see the correct values in the drop down menu. I would like all
> the drop downs to automaticaaly update.
>
> In the drop downs which work in a1 you can see the formula "=c1", in the
> others you only see the value of C1 any assistance greatly appreciated.
>
> "Debra Dalgleish" wrote:
>
> > If you give an example of the layout, and the formulas that you're using
> > in the Data Validation cells, someone may be able to help.
> >
> > Buzz wrote:
> > > I have a spreadsheet where i have created a number of drop down lists using
> > > data validation. These lists refer to cells which in turn refer to other
> > > cells. I some of the Drop downs I created the cells update due to changes in
> > > data elsewhere in the sheet, however in others they don't., I wouls like them
> > > all to update.
> > >
> > > I cant see what I doing wrong any assistance great fully recieved.
> >
> >

--

Dave Peterson

Dave,

Thank you this works, the thing with excel there are so many different ways
to skin the proverbial cat. Thanks again.

"Dave Peterson" wrote:

> I don't think that there's anything inside Data|Validation that will change the
> value of a cell when the list changes.
>
> In fact, lots of lists can are based on dynamic ranges--that grow and contract
> when the number of items in the list are added/deleted. It would be pretty
> difficult to make sure you cover every contigency.
>
> And even more...You can use data|validation as a warning tool. You can let the
> user type something in that cell with data|validation that doesn't appear on the
> list.
>
> It could be quite a pain to come up with something that would work all the time
> (although, maybe under specific circumstancs, you could have something that
> worked fine).
>
> But it might be easier to drop the data|validation and use a combobox from the
> Control toolbox toolbar.
>
> Put it over cell A1.
>
> While in design mode (another icon on that control toolbox toolbar), right click
> on it and choose properties.
>
> Set the style to fmStyleDropDownList (to only allow choosing from the dropdown)
>
> Assign the linked cell to A1 (so you can use it other formulas???)
>
> Assign the ListFillRange to B1:B3.
>
> Now if the combobox has the value equal to what's in B1 (which points at C1),
> and C1 changes, the value in the combobox (and A1) will change.
>
> This seems a lot simpler than struggling with Data|Validation--well, to me
> anyway.
>
> Buzz wrote:
> >
> > Sorry new to this discussion group thing , I did not want to overburden with
> > detail intially, but below is more detail
> >
> > cell A1 has a drop down which references cells b1:b3, cell b1 contains a
> > formula "=c1", in some of the drop downs when c1 changes the drop down menu
> > automatically updates, and is some its only when you select the drop down
> > menu do you see the correct values in the drop down menu. I would like all
> > the drop downs to automaticaaly update.
> >
> > In the drop downs which work in a1 you can see the formula "=c1", in the
> > others you only see the value of C1 any assistance greatly appreciated.
> >
> > "Debra Dalgleish" wrote:
> >
> > > If you give an example of the layout, and the formulas that you're using
> > > in the Data Validation cells, someone may be able to help.
> > >
> > > Buzz wrote:
> > > > I have a spreadsheet where i have created a number of drop down lists using
> > > > data validation. These lists refer to cells which in turn refer to other
> > > > cells. I some of the Drop downs I created the cells update due to changes in
> > > > data elsewhere in the sheet, however in others they don't., I wouls like them
> > > > all to update.
> > > >
> > > > I cant see what I doing wrong any assistance great fully recieved.
> > >
> > >
>
> --
>
> Dave Peterson
>