Can INDIRECT function reference a cell that contains a formula

[Steve E]

I am trying to limit data entry options into a worksheet based on other
information already 'correctly' entered. Based on a number of criteria I
return a Named Range as a result of the formula:

I am using =INDIRECT(AC18) as the data validation formula

Cell AC18:
=IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'Input
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected,'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP(SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected,'Input
Lists'!$A$3:$E$40,5,FALSE),"Get
Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelected,'Input
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected,'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP(SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected,'Input
Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))))

The resulting named ranges in the 'InputLists'!$A$3:$E$40 are BracketList_A,
BracketList_B and BracketList_C where these named ranges containg the
brackets that are appropriate for selection by the user. These named ranges
are Dynamic Ranges allowing us to update the data table as needed.

Based on what I'd read in Debra Dalgleish's site and in the help files I
thought that this would work... I don't get any errors but the drop down
list in the data validation is empty.

Too much info?

 

[Biff]

Hi!

Using Indirect and dynamic ranges won't work. The reason the drop down
doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error.

Try this as the drop down source:

List the named ranges somewhere,say, G1:I1.

G1 = listA
H1 = listB
I1 = listC

Or, you may already have the names listed somewhere as the column headers
for the named ranges. If so just refer to those cells:

=CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC)

You can have up to 29 named ranges and use CHOOSE.

When your formula returns "Get Quote" that'll cause the source formula to
error and the drop down won't work.

Biff

 

[Steve E]

Hi BIff,

Thanks for the help. Unfortunately, I seem to only get listB to populate
the drop down... even though cell AC18 evaluates to listA...

Any ideas?

And... wouldn't it be nice if you could cut and paste into the formula bar
in the data validation box?

Appreciate your help.

Steve

 

[Steve E]

Biff,

When I tried the same formula in a blank cell on my worksheet it evaluated
to a $VALUE error. When I put the listA, listB, listC named ranges in quotes
ie "listA, "listB", "listC" it returned the correct match... but when I tried
this same thing in the data validation formula bar I get an error (of
course, non-traceable).

??

 

[Biff]

The formula won't work in a worksheet cell.

DON'T enclose the range names in quotes. They'll be evaluated as TEXT
strings.

See my other reply!

Biff