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

Data validation formulas can't use UDFs, and won't even recognize the
functions in the Analysis Toolpak.

Perhaps you can create a custom formula, instead of your UDF.

CodeSponge wrote:
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



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html