Problem with INDIRECT and Dynamic Lists 1

[pmcmicha]

I am attempting to reduce the amount of maintenance on a worksheet by making certain columns dynamic in regards to their named range. Then I wish to reference this from a couple of lists.

Dynamic Named Range: TEST

=INDIRECT("DATA!$A$2:$A"&ROW(OFFSET(DATA!$A$2,COUNTA(DATA!$A$2:$A$50)-1,0)))

** This will find the last text entry in that column, so I do not have to update the named range constantly.

List Box 1: (CELL: Sheet1!C2)

="NOTHING","SOMETHING","TEST"

List Box 2: (CELL: Sheet1!D2)

=INDIRECT("TEST_"&$C$2)

If I use the Dynamic Named Range, then List Box 2 does not work, but if the Named Range is set manually, then this setup works just fine. Troubleshooting:
1) Removed INDIRECT from Dynamic Named Range, no effect. Readded.
2) Removed INDIRECT from List Box 2, no effect. Readded.
3) Removed both INDIRECT's, no effect.

Is there a workaround for this problem? Thanks in advance.

 

[xlbo]

There is no way to use INDIRECT with a dynamic range reference

Only realistic way round it is to use VBA to change range references

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

The only workaround I've used is explained here:

http://www.contextures.com/xlDataVal02.html#Dynamic

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.