Indirect Drop Down Lists

[Angus95]

Hi,

I am using Indirect Drop Down lists for my project. =indirect(e30) etc.

I have a rage of name ranges in the name manager. I am using this sort of formula to adjust the lists as things are added or removed:

=Sheet1!$C$2:INDEX(Sheet1!$C$2:$C$1000,SUMPRODUCT(--(Sheet1!$C$2:$C$1000<>"")))

My indirect drop down list wont work when i use that formula.

Any help?

Angus

 

[pgc01]

Hi Angus

Define your lists as Tables.
They will adjust if you add or delete values.

Then define a name like

DropdownList
=INDIRECT(Sheet1!$E$3)

In E3 write the name of the table and in the data validation, in the List:

=DropdownList

 

[Angus95]

Hi PGC,

The table seems to automatically adjust if I add values, but doesn't adjust if I delete value?

 

[pgc01]

Hi

Yes it does if you delete the cell.

Maybe you are just clearing the value?

In the cell right-click and choose delete.

 

[Angus95]

Hi,

Sure you can do that, but that removes my point of wanting it all automated.

I want to make it so anyone (computer illiterate or not) can pretty much use the spreadsheet.

Lots of people I work with arent the best with computers let along Excel.

 

[pgc01]

In that case you can use the formula that you posted in Post #1 and use an auxiliary name to evaluate it.

Define
Name: IndirectF3
Refers to: =EVALUATE(Sheet1!$F$3)

and in the DataValidation List:
=IndirectF3

and in F3 you write the range name.