I am attempting to create a Data Validation formula to test an input cell to
make sure that it can be used to automatically assign a file name.

I tried putting the Data Validation "custom" formula attached to cell B2,
but all text comes back as invalid.
InStr(B2,"?"&Chr(34)&"/\<>*|:")=0

Any ideas on why this fails?

I.E.
" used here to show the string limits... not part of the actual string,
although Chr(34) would be an invalid filename character in the InStr above.
"Test" in the cell SHOULD be valid
"Test<1>" SHOULD NOT be valid
--
Regards,
John

Hmmm....I couldn't get Validation to work with this huge formula, so I just
placed it in a cell and referenced the formula cell:

Put this in a cell (say E1):

=SUMPRODUCT(--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))={34,42,47,58,60,62,63,92,124}))=0

Now in your Validation cell (formula assumes A1), choose Allow:Custom and in
Formula put:

=E1

HTH
Jason
Atlanta, GA


"John Keith" wrote:

> I am attempting to create a Data Validation formula to test an input cell to
> make sure that it can be used to automatically assign a file name.
>
> I tried putting the Data Validation "custom" formula attached to cell B2,
> but all text comes back as invalid.
> InStr(B2,"?"&Chr(34)&"/\<>*|:")=0
>
> Any ideas on why this fails?
>
> I.E.
> " used here to show the string limits... not part of the actual string,
> although Chr(34) would be an invalid filename character in the InStr above.
> "Test" in the cell SHOULD be valid
> "Test<1>" SHOULD NOT be valid
> --
> Regards,
> John

Thanks for the reply.

That is a slick formula!
At first I wasnt sure If your responce was to "my" question

I tried it and it does work. I think the DataValidation does not allow
array formulas (or perhaps it was the Indirect reference)

However I wanted to have a formula that would be easier for others to
understand & maintain later, so I ended up going with a direct formula which
I used a macro to assign (because paste wasn't allowed into the formula box)

sDV = "=AND(ISERROR(FIND(""?"",B2)),ISERROR(FIND(CHAR(34),B2))," & _
"ISERROR(FIND(""/"",B2)),ISERROR(FIND(""\"",B2))," & _
"ISERROR(FIND(""<"",B2)),ISERROR(FIND("">"",B2))," & _

"ISERROR(FIND(""*"",B2)),ISERROR(FIND(""|"",B2)),ISERROR(FIND("":"",B2)))"

--- To bad the Find() does not simply return 0 when the text is not found

Assigned using the
.validation.add method ... Formula1:=sDV

--
Regards,
John


"Jason Morin" wrote:

> Hmmm....I couldn't get Validation to work with this huge formula, so I just
> placed it in a cell and referenced the formula cell:
>
> Put this in a cell (say E1):
>
> =SUMPRODUCT(--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))={34,42,47,58,60,62,63,92,124}))=0
>
> Now in your Validation cell (formula assumes A1), choose Allow:Custom and in
> Formula put:
>
> =E1
>
> HTH
> Jason
> Atlanta, GA
>
>
> "John Keith" wrote:
>
> > I am attempting to create a Data Validation formula to test an input cell to
> > make sure that it can be used to automatically assign a file name.
> >
> > I tried putting the Data Validation "custom" formula attached to cell B2,
> > but all text comes back as invalid.
> > InStr(B2,"?"&Chr(34)&"/\<>*|:")=0
> >
> > Any ideas on why this fails?
> >
> > I.E.
> > " used here to show the string limits... not part of the actual string,
> > although Chr(34) would be an invalid filename character in the InStr above.
> > "Test" in the cell SHOULD be valid
> > "Test<1>" SHOULD NOT be valid
> > --
> > Regards,
> > John