Problem with Data Validation

[George]

Hi, I know how to do Data Validation but I'm coming across a problem that I'm
really stumped with. I've looked around to see if anyone else has had this
problem but can't find any solutions at the moment - especially as "INDIRECT"
doesn't seem to be working for me in this instance.

I've got a worksheet with formulas on that links to another worksheet to
pull names of staff members in. These staff members all have an associated
shift which they work on. The data looks like this...

Shift Name
ES Tester1
ES Tester2
LS Tester 3
LS Tester4
NS Tester5

The names are being brought in using the formulas correctly and the
associated named ranges (ESTesters, LSTesters) are all working properly using
OFFSET to calculate where to start and end in the list. The whole spreadsheet
has a cell named "AllocationShift" on the front page, where users can state
"ES", "LS" or "NS".

Now if I specify a cell to have Data Validation with
"INDIRECT(AllocationShift & 'Testers')" Excel says that the Source evaluates
to an error. Putting the same formula in a cell and stepping through it looks
like it doesn't like "INDIRECT('ESTesters')" - that's certainly the step
where it gives me #REF. Now I think this means that it can't find the named
range but I'm not sure.

This formula used to work when i was importing the data via macros but
that's since stopped because the document is shared and we were getting too
many "automation problems" through different versions of Excel. I also
suspect that our network may have been causing some of the problems.

Anyway - this error might be caused that the named range "ESTesters"
contains formulas, rather than text, but again I'm unsure. If anyone has any
advice, or wants to see code then give me a shout!

Thanks very much - this forum has provided me with excellent support before
now, and I'm not expecting this to change any time soon
George

 

[George]

Just a little further information...

If I put "=ESTesters" into the DV it works :-(

It's just I can't make the list validation work when I want to change the
named range based on the AllocationShift cell :-(

 

[T. Valko]

this error might be caused that the named range "ESTesters"

contains formulas, rather than text,

Close!

If ESTesters is a dynamic range calculated by using OFFSET then:

=INDIRECT(ESTesters) = #REF!

The problem is INDIRECT's argument *must* be a TEXT representation of a
valid reference. In this case the argument evaluates to the OFFSET formula
causing the error.