change in-cell list with IF instruction

  • Thread starter Thread starter Excel-lent
  • Start date Start date
E

Excel-lent

Hello people,

I have two name-lists defined.
Let's call them "List 1" and "List 2".

When the value of cell A1 = "1", then List 1 has to appear in cell A2 as an
in-cell dropdown.
When the value of cell A1 = "2", then List 2 has to appear in cell A2 as an
in-cell dropdown.

I wanted to do this with an IF instruction in my formula but that doesn't
seem to work.
Any ideas how to change the contents of an in-cell dropdown, according to a
certain other cell value?

Thanks in advance!
 
Hi

Define your list as named ranges, let's be List1 and List2.
Create a 3rd named range, p.e. Selection. With your dropdowns on sheet
Sheet1:
=IF(Sheet1!$A$1=2,List2,List1)
Define the source for dropdown in A2 as
=Source
 
Hello Arvi,

Thanks for your help, but I don't quite get it.

How can I enter "=Source" as a source? This is a name range that doesn't
exist.
Perhaps you mean to put "=Selection" as the source? But then what do I have
to put in that 3rd named range?

Sorry, I hope you or someone else can explain it more clearly/in a different
way?

Thanks
 
Hi
try the following formula as source for your list in the data
validation dialog
=INDIRECT("List " & $A$1)
 
Supposing that you want to data validate B1 and your lists are List1 and
List2 (no spaces in the list names)...

Activate B1.
Activate Data|Validation.
Choose List for Allow.
Enter in the Source box:

=CHOOSE(A1,List1,List2)

Click OK.

I assumed that A1, which can house either 1 or 2, and B1, the data
validation cell, are on the same sheet. Note also that there is noc check
for A1 housing anything other than 1 or 2.
 
works like a charm, thanks a lot, Frank!


Frank Kabel said:
Hi
try the following formula as source for your list in the data
validation dialog
=INDIRECT("List " & $A$1)
Click to expand...
 
Hi


Excel-lent said:
Hello Arvi,

Thanks for your help, but I don't quite get it.

How can I enter "=Source" as a source? This is a name range that doesn't
exist.
Click to expand...

I send a correction just. Of-course it must be Selection
Perhaps you mean to put "=Selection" as the source? But then what do I have
to put in that 3rd named range?
Click to expand...

The formula
=IF(Sheet1!$A$1=2,List2,List1)
i.e. when you have 2 selected in A1, the range Selection equals range List2,
otherwise range List1.
 
Aladin Akyurek said:
An expensive function as INDIRECT can be easily avoided here.
Click to expand...

totally agree :-)
for the OP: you may use Aladin's function

Frank
P.S.: Aladin have you concrete numbers about the expensiveness of
INDIRECT - just curious
 
Back
Top