Re: Linking Drop-down options

Tech-Archive recommends: Speed Up your PC by fixing your registry

So, you want dependent drop downs?

Try this:

List all the numbers in a range of cells:

F1:F9 = 1,2,3,4,5,6,7,8,9
F10:F28 = 17,18,19...35

Give the range F1:F5 the name Circles
Give the range F3:F9 the name Squares
Give the range F10:F28 the name Rectangles

Assume the main drop down list is in cell A1 and has these selections
available: Circles, Squares, Rectangles.

As the source for the dependent drop down use this formula:

=INDIRECT(A1)

You might also want to do something about what is displayed in the dependent
drop down cell when a different selection is made from the main drop down.
For example, you select Rectangles from the main list then you select 30
from the dependent list. You then make a different selection from the main
list, Circles. 30 still appears in the dependent cell but this is not a
valid selection for Circles.

You can "fix" this a couple of ways. Use an event macro to clear the
dependent cell when a change is made to the main list or use conditional
formatting to "hide" the invalid selection in the dependent cell.

Post back if you want to use one of those options and need help figuring it
out.


--
Biff
Microsoft Excel MVP


"John1791" <John1791@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A4A017CE-53AF-4165-90B9-BDE84426EA48@xxxxxxxxxxxxxxxx
I am trying to link multiple cells and can not figure out how to do this
properly.

Example:

One cell has a drop down menu that has choices of: circles, squares, or
rectangles. But the next cell requires a number within a certian range,
but
this range is different depending on which of the first cell was chosen.

circles have to be between 1 and 5
squares have to be between 3 and 9
and rectangles have to be between 17 and 35

I can not figure out how to do this....can anyone help?

Thanks,

John


.

Relevant Pages

  • Re: Wynne-Edwards ( was Homosexuality)
    ... >> levels of selection, I mean that I think fitnesses at those levels are ... >>> cannot heuristically refer to the fitness of just a single trait. ... > I would put that more concisely: a trait is a dependent entity which is just ... > cell forms the stalk wherein it does not reproduce at all. ...
    (sci.bio.evolution)
  • Re: Problem with Nothing command
    ... If I have a cell that has a dependent, ... Dim Rng As Range ... 'Set Container to nothing to make sure that if it becomes something ...
    (microsoft.public.excel.programming)
  • Re: Trace Dependents
    ... > normal cell reference and a named cell in Sheet1 and a dependent in ... > the cell at the offset position. ... If the precedent and dependent are in the ... >> I was assuming that these functions were normal EXCEL functions that I ...
    (microsoft.public.excel.programming)
  • Re: VBA newbie needs a little help
    ... >> click into a cell with a data validation list. ... >> for the listfillrange for the two dependent lists. ... >> problem with this is that I named my two word ranges as one word, ...
    (microsoft.public.excel.programming)
  • Re: Does anyone know how to force a cell re-calculation
    ... I'm doing and my code is in my UDF. ... I'm want to force the re-calculation of a single cell, ... would like it to recalculate even if the dependent data hasn't changed. ...
    (microsoft.public.excel.programming)