I don't get it, Whats going on here?

[CodeSponge]

What's wrong here?

I'm adding validation to say 3 cells using dropdown boxes.
To do this I'm using Named Ranges like so:

List1 (Category)
Communications
Customer Inquiry
Equipment

List2 (CustomerInquiry)
Dispatched Call
Follow-up
Informational

List3 (Followup)
30 Day Follow-up
60 Day Follow-up
90 Day Follow-up

To do this I use INDIRECT() to look at the previous cell
to find the name of the list to use for the current cells validation.

Named Ranges can't have spaces, or special chars in them
i.e.: " " or "-"
So I named the ranges by removing them. "Follow-up" =>
"Followup"

Now, to find the right name you would normal use
=INDIRECT(SUBSTITUE(A1, "-", ""))
Which works fine, but I need to use a regular expression to remove
specific patterns of chars.
To do this I made a user defined function called REReplace(sPattern,
sTest, sReplacement)
The function works great if, lets say, I use it like =REReplace(A1,
"&amp:", "")
The result is exactly what you would expect.
************
HOWEVER!!
************
When I nest it inside INDIRECT() like =INDIRECT(REReplace(A1,
"&amp:", ""))
The validation dialog box tells me I'm dumb and won't let me enter
it.

Why is it that I can use
=INDIRECT(SUBSTITUE(A1, "-", ""))
but not
=INDIRECT(REReplace(A1, "&amp:", ""))
when I can use
=REReplace(A1, "&amp:", "")
just fine?

I've gotten a test case to work by setting up an intermediate cell to
store the conversion like
cell1 = REReplace(A1, "[^A-Za-z0-9]", "")
cell2 = INDIRECT(cell1)
But besides being a sloppy workaround, it's impractical for this
project.

Does anyone have any Ideas on what's going on here?
And maybe on how to fix it?

I'd appresiate the help
Shaun Kohanowski

 

[Tom Ogilvy]

My guess is that it takes two levels of evaluation and that doesn't work in
data validation.

That has been my experience anyway.

You might want to post this over in WorksheetFunctions and see if they have
a better explanation for you.

 

[CodeSponge]

hehe
I went and popped into the WorksheetFunctions group and reposted the Q.
It looks like that group has seen better days.
For the last so many months its been lucky to get a post every other
day.
Hope some one has the time to reading it.

 

[Tom Ogilvy]

It should be worksheet.Functions

This replaced the old WorksheetFunctions and conformed with internet
standards. Sorry about the confusion.
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions