Data Validation Using Indirect and Variable Range!

[sxhall]

Hi,

I am using the following to determine named ranges based upon certain texts. The formula i have used finds the first and last instance in column C of the text and then using INDIRECT it sets the range to be used...

Named Range "ListAAA" - =INDIRECT("Criteria!C" & MATCH("AAA",Criteria!E:E,0) & ":C" & SUMPRODUCT(MAX((Criteria!E:E="AAA")*(ROW(Criteria!E:E)))))
Named Range "ListBBB" - =INDIRECT("Criteria!C" & MATCH("BBB",Criteria!E:E,0) & ":C" & SUMPRODUCT(MAX((Criteria!E:E="BBB")*(ROW(Criteria!E:E)))))
Named Range "ListCCC" - =INDIRECT("Criteria!C" & MATCH("CCC",Criteria!E:E,0) & ":C" & SUMPRODUCT(MAX((Criteria!E:E="CCC")*(ROW(Criteria!E:E)))))
Named Range "ListDDD" - =INDIRECT("Criteria!C" & MATCH("DDD",Criteria!E:E,0) & ":C" & SUMPRODUCT(MAX((Criteria!E:E="DDD")*(ROW(Criteria!E:E)))))

On another worksheet I have a cell that allows you to select ether "AAA", "BBB", "CCC" or "DDD" using data validation. From this in the cell below I am trying to use data validation again to with =INDIRECT("List" & $I$2).

However no matter what I try it does not populate the second dropdown.

When I go in to data validation it tells me that it calculates in to an error. But when I view the named range it selects an area based upon the formulas at used.

Any ideas?

Tried the above with a fixed range against the named ranges but they will need to be variable as users add or remove rows the worksheet.


Thanks in advance for any help.

Steven

 

[pgc01]

Hi Steven

Try the following:

Define the name:

MyList: =OFFSET(Criteria!$C$1,MATCH(!$I$2,Criteria!$E:$E,0)-1,,COUNTIF(Criteria!$E:$E,!$I$2))

In data validation use: =MyList

 

[sxhall]

Thanks PGC.

This is brilliant and I only need the one named range. Will have to set about understanding how OFFSET set works now as it a function I've never really used before.

Steven

 

[pgc01]

You're welcome. Thanks for the feedback.