Data Validation question

[anny]

hi gurus

I have defined a Name for a function, WS_Name, that returns the name of the
active worksheet (eg A6-X, A4-R, B7-Q ...etc).

On each worksheet, I want to add validation to column C. The value in colum
C must be =MID(WS_Name,2,1). In the 3 sheets mentioned above, column C
should accept ONLY values of 6, 4 and 7 respectively.

I can't get the validation to work. I'm trying ...

Data>Validation>Settings> Allow: Custom, Formula: =MID(WS_Name,2,1)
or

Data>Validation>Settings> Allow: Custom, Formula:
=VALUE(MID(WS_Name,2,1))

No luck so far. Column C still takes any value. Any ideas?

TQ, Anny

[Biff]

Hi!

Well, I'm assuming that your named formula works properly.

You have to refer to the cell that is being validated:

=C1=MID(WS_Name,2,1)

Now, the MID function returns TEXT, so if:

=MID(WS_Name,2,1)


Returned 6, that 6 is TEXT and if the user entered a numeric 6 in cell C1 =
rejected!

So, try this:

=C1=--MID(WS_Name,2,1)

Or, format the target cell as TEXT (don't know if you really want to do
that, though!)

Biff

"anny" <[email protected]> wrote in message
news:[email protected]...
> hi gurus
>
> I have defined a Name for a function, WS_Name, that returns the name of
> the active worksheet (eg A6-X, A4-R, B7-Q ...etc).
>
> On each worksheet, I want to add validation to column C. The value in
> colum C must be =MID(WS_Name,2,1). In the 3 sheets mentioned above,
> column C should accept ONLY values of 6, 4 and 7 respectively.
>
> I can't get the validation to work. I'm trying ...
>
> Data>Validation>Settings> Allow: Custom, Formula: =MID(WS_Name,2,1)
> or
>
> Data>Validation>Settings> Allow: Custom, Formula:
> =VALUE(MID(WS_Name,2,1))
>
> No luck so far. Column C still takes any value. Any ideas?
>
> TQ, Anny
>
>
>

[anny]

Hello

Biff - I couldn't get this to work.

> You have to refer to the cell that is being validated:
> =C1=MID(WS_Name,2,1)
>
Also, I'm not sure that the cell needs to be refered to as you've indicated.
When I simply use =1 as the validation criteria in column C, it works
fine. Also, the named function works properly. When I write
=VALUE(MID(WS_Name,2,1) in any cell, I get the desired number. I just
can't get this expression to work as a validation criteria.

Any ideas out there?
Anny


"Biff" <[email protected]> wrote in message
news:uo%[email protected]...
> Hi!
>
> Well, I'm assuming that your named formula works properly.
>
> You have to refer to the cell that is being validated:
>
> =C1=MID(WS_Name,2,1)
>
> Now, the MID function returns TEXT, so if:
>
> =MID(WS_Name,2,1)
>
>
> Returned 6, that 6 is TEXT and if the user entered a numeric 6 in cell C1
> = rejected!
>
> So, try this:
>
> =C1=--MID(WS_Name,2,1)
>
> Or, format the target cell as TEXT (don't know if you really want to do
> that, though!)
>
> Biff
>
> "anny" <[email protected]> wrote in message
> news:[email protected]...
>> hi gurus
>>
>> I have defined a Name for a function, WS_Name, that returns the name of
>> the active worksheet (eg A6-X, A4-R, B7-Q ...etc).
>>
>> On each worksheet, I want to add validation to column C. The value in
>> colum C must be =MID(WS_Name,2,1). In the 3 sheets mentioned above,
>> column C should accept ONLY values of 6, 4 and 7 respectively.
>>
>> I can't get the validation to work. I'm trying ...
>>
>> Data>Validation>Settings> Allow: Custom, Formula: =MID(WS_Name,2,1)
>> or
>>
>> Data>Validation>Settings> Allow: Custom, Formula:
>> =VALUE(MID(WS_Name,2,1))
>>
>> No luck so far. Column C still takes any value. Any ideas?
>>
>> TQ, Anny
>>
>>
>>
>
>

[Biff]

Post the actual formula for WS_Name so I can do some testing.

Biff

"anny" <[email protected]> wrote in message
news:[email protected]...
> Hello
>
> Biff - I couldn't get this to work.
>
>> You have to refer to the cell that is being validated:
>> =C1=MID(WS_Name,2,1)
>>
> Also, I'm not sure that the cell needs to be refered to as you've
> indicated. When I simply use =1 as the validation criteria in column
> C, it works fine. Also, the named function works properly. When I write
> =VALUE(MID(WS_Name,2,1) in any cell, I get the desired number. I just
> can't get this expression to work as a validation criteria.
>
> Any ideas out there?
> Anny
>
>
> "Biff" <[email protected]> wrote in message
> news:uo%[email protected]...
>> Hi!
>>
>> Well, I'm assuming that your named formula works properly.
>>
>> You have to refer to the cell that is being validated:
>>
>> =C1=MID(WS_Name,2,1)
>>
>> Now, the MID function returns TEXT, so if:
>>
>> =MID(WS_Name,2,1)
>>
>>
>> Returned 6, that 6 is TEXT and if the user entered a numeric 6 in cell C1
>> = rejected!
>>
>> So, try this:
>>
>> =C1=--MID(WS_Name,2,1)
>>
>> Or, format the target cell as TEXT (don't know if you really want to do
>> that, though!)
>>
>> Biff
>>
>> "anny" <[email protected]> wrote in message
>> news:[email protected]...
>>> hi gurus
>>>
>>> I have defined a Name for a function, WS_Name, that returns the name of
>>> the active worksheet (eg A6-X, A4-R, B7-Q ...etc).
>>>
>>> On each worksheet, I want to add validation to column C. The value in
>>> colum C must be =MID(WS_Name,2,1). In the 3 sheets mentioned above,
>>> column C should accept ONLY values of 6, 4 and 7 respectively.
>>>
>>> I can't get the validation to work. I'm trying ...
>>>
>>> Data>Validation>Settings> Allow: Custom, Formula: =MID(WS_Name,2,1)
>>> or
>>>
>>> Data>Validation>Settings> Allow: Custom, Formula:
>>> =VALUE(MID(WS_Name,2,1))
>>>
>>> No luck so far. Column C still takes any value. Any ideas?
>>>
>>> TQ, Anny
>>>
>>>
>>>
>>
>>
>
>

[anny]

Hi Biff

Here's the named formula WS_Name you requested

=MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32)

It properly identifies the name of the worksheet when placed in a cell.

Thanks for your efforts
Anny


"Biff" <[email protected]> wrote in message
news:[email protected]...
> Post the actual formula for WS_Name so I can do some testing.
>
> Biff
>
> "anny" <[email protected]> wrote in message
> news:[email protected]...
>> Hello
>>
>> Biff - I couldn't get this to work.
>>
>>> You have to refer to the cell that is being validated:
>>> =C1=MID(WS_Name,2,1)
>>>
>> Also, I'm not sure that the cell needs to be refered to as you've
>> indicated. When I simply use =1 as the validation criteria in column
>> C, it works fine. Also, the named function works properly. When I write
>> =VALUE(MID(WS_Name,2,1) in any cell, I get the desired number. I just
>> can't get this expression to work as a validation criteria.
>>
>> Any ideas out there?
>> Anny
>>
>>
>> "Biff" <[email protected]> wrote in message
>> news:uo%[email protected]...
>>> Hi!
>>>
>>> Well, I'm assuming that your named formula works properly.
>>>
>>> You have to refer to the cell that is being validated:
>>>
>>> =C1=MID(WS_Name,2,1)
>>>
>>> Now, the MID function returns TEXT, so if:
>>>
>>> =MID(WS_Name,2,1)
>>>
>>>
>>> Returned 6, that 6 is TEXT and if the user entered a numeric 6 in cell
>>> C1 = rejected!
>>>
>>> So, try this:
>>>
>>> =C1=--MID(WS_Name,2,1)
>>>
>>> Or, format the target cell as TEXT (don't know if you really want to do
>>> that, though!)
>>>
>>> Biff
>>>
>>> "anny" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> hi gurus
>>>>
>>>> I have defined a Name for a function, WS_Name, that returns the name of
>>>> the active worksheet (eg A6-X, A4-R, B7-Q ...etc).
>>>>
>>>> On each worksheet, I want to add validation to column C. The value in
>>>> colum C must be =MID(WS_Name,2,1). In the 3 sheets mentioned above,
>>>> column C should accept ONLY values of 6, 4 and 7 respectively.
>>>>
>>>> I can't get the validation to work. I'm trying ...
>>>>
>>>> Data>Validation>Settings> Allow: Custom, Formula:
>>>> =MID(WS_Name,2,1) or
>>>>
>>>> Data>Validation>Settings> Allow: Custom, Formula:
>>>> =VALUE(MID(WS_Name,2,1))
>>>>
>>>> No luck so far. Column C still takes any value. Any ideas?
>>>>
>>>> TQ, Anny
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

[Biff]

OK...........

I don't know why:

=C1=--MID(WS_name,2,1)

won't work. It works just fine when I test it in cells on the worksheet
itself but when applied as the formula in data validation it doesn't work. I
suspect it has something to do with the named formula.

This WILL work:

=C1=--MID(MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32),2,1)

Or, put this formula in some out of the way cell like AA1:

=--MID(WS_name,2,1)

and then refer to that cell:

=C1=AA1

Side note:

=VALUE(MID(WS_Name,2,1))
=--MID(WS_Name,2,1)

do the exact same thing.

Biff

"anny" <[email protected]> wrote in message
news:[email protected]...
> Hi Biff
>
> Here's the named formula WS_Name you requested
>
> =MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32)
>
> It properly identifies the name of the worksheet when placed in a cell.
>
> Thanks for your efforts
> Anny
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> Post the actual formula for WS_Name so I can do some testing.
>>
>> Biff
>>
>> "anny" <[email protected]> wrote in message
>> news:[email protected]...
>>> Hello
>>>
>>> Biff - I couldn't get this to work.
>>>
>>>> You have to refer to the cell that is being validated:
>>>> =C1=MID(WS_Name,2,1)
>>>>
>>> Also, I'm not sure that the cell needs to be refered to as you've
>>> indicated. When I simply use =1 as the validation criteria in column
>>> C, it works fine. Also, the named function works properly. When I
>>> write =VALUE(MID(WS_Name,2,1) in any cell, I get the desired number.
>>> I just can't get this expression to work as a validation criteria.
>>>
>>> Any ideas out there?
>>> Anny
>>>
>>>
>>> "Biff" <[email protected]> wrote in message
>>> news:uo%[email protected]...
>>>> Hi!
>>>>
>>>> Well, I'm assuming that your named formula works properly.
>>>>
>>>> You have to refer to the cell that is being validated:
>>>>
>>>> =C1=MID(WS_Name,2,1)
>>>>
>>>> Now, the MID function returns TEXT, so if:
>>>>
>>>> =MID(WS_Name,2,1)
>>>>
>>>>
>>>> Returned 6, that 6 is TEXT and if the user entered a numeric 6 in cell
>>>> C1 = rejected!
>>>>
>>>> So, try this:
>>>>
>>>> =C1=--MID(WS_Name,2,1)
>>>>
>>>> Or, format the target cell as TEXT (don't know if you really want to do
>>>> that, though!)
>>>>
>>>> Biff
>>>>
>>>> "anny" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> hi gurus
>>>>>
>>>>> I have defined a Name for a function, WS_Name, that returns the name
>>>>> of the active worksheet (eg A6-X, A4-R, B7-Q ...etc).
>>>>>
>>>>> On each worksheet, I want to add validation to column C. The value in
>>>>> colum C must be =MID(WS_Name,2,1). In the 3 sheets mentioned above,
>>>>> column C should accept ONLY values of 6, 4 and 7 respectively.
>>>>>
>>>>> I can't get the validation to work. I'm trying ...
>>>>>
>>>>> Data>Validation>Settings> Allow: Custom, Formula:
>>>>> =MID(WS_Name,2,1) or
>>>>>
>>>>> Data>Validation>Settings> Allow: Custom, Formula:
>>>>> =VALUE(MID(WS_Name,2,1))
>>>>>
>>>>> No luck so far. Column C still takes any value. Any ideas?
>>>>>
>>>>> TQ, Anny
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

[anny]

Biff RULES!

anny

[Peo Sjoblom]

It's because if you use for instance indirect or offset that refers to
another cell (in this case I believe it is A1) you need to uncheck ignore
blanks in the validation window, I think =C1=--MID(WS_Name,2,1)
will work as validation if you do that.

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Biff" <[email protected]> wrote in message
news:%[email protected]...
> OK...........
>
> I don't know why:
>
> =C1=--MID(WS_name,2,1)
>
> won't work. It works just fine when I test it in cells on the worksheet
> itself but when applied as the formula in data validation it doesn't work.
> I suspect it has something to do with the named formula.
>
> This WILL work:
>
> =C1=--MID(MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32),2,1)
>
> Or, put this formula in some out of the way cell like AA1:
>
> =--MID(WS_name,2,1)
>
> and then refer to that cell:
>
> =C1=AA1
>
> Side note:
>
> =VALUE(MID(WS_Name,2,1))
> =--MID(WS_Name,2,1)
>
> do the exact same thing.
>
> Biff
>
> "anny" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Biff
>>
>> Here's the named formula WS_Name you requested
>>
>> =MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32)
>>
>> It properly identifies the name of the worksheet when placed in a cell.
>>
>> Thanks for your efforts
>> Anny
>>
>>
>> "Biff" <[email protected]> wrote in message
>> news:[email protected]...
>>> Post the actual formula for WS_Name so I can do some testing.
>>>
>>> Biff
>>>
>>> "anny" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> Hello
>>>>
>>>> Biff - I couldn't get this to work.
>>>>
>>>>> You have to refer to the cell that is being validated:
>>>>> =C1=MID(WS_Name,2,1)
>>>>>
>>>> Also, I'm not sure that the cell needs to be refered to as you've
>>>> indicated. When I simply use =1 as the validation criteria in
>>>> column C, it works fine. Also, the named function works properly.
>>>> When I write =VALUE(MID(WS_Name,2,1) in any cell, I get the desired
>>>> number. I just can't get this expression to work as a validation
>>>> criteria.
>>>>
>>>> Any ideas out there?
>>>> Anny
>>>>
>>>>
>>>> "Biff" <[email protected]> wrote in message
>>>> news:uo%[email protected]...
>>>>> Hi!
>>>>>
>>>>> Well, I'm assuming that your named formula works properly.
>>>>>
>>>>> You have to refer to the cell that is being validated:
>>>>>
>>>>> =C1=MID(WS_Name,2,1)
>>>>>
>>>>> Now, the MID function returns TEXT, so if:
>>>>>
>>>>> =MID(WS_Name,2,1)
>>>>>
>>>>>
>>>>> Returned 6, that 6 is TEXT and if the user entered a numeric 6 in cell
>>>>> C1 = rejected!
>>>>>
>>>>> So, try this:
>>>>>
>>>>> =C1=--MID(WS_Name,2,1)
>>>>>
>>>>> Or, format the target cell as TEXT (don't know if you really want to
>>>>> do that, though!)
>>>>>
>>>>> Biff
>>>>>
>>>>> "anny" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> hi gurus
>>>>>>
>>>>>> I have defined a Name for a function, WS_Name, that returns the name
>>>>>> of the active worksheet (eg A6-X, A4-R, B7-Q ...etc).
>>>>>>
>>>>>> On each worksheet, I want to add validation to column C. The value
>>>>>> in colum C must be =MID(WS_Name,2,1). In the 3 sheets mentioned
>>>>>> above, column C should accept ONLY values of 6, 4 and 7 respectively.
>>>>>>
>>>>>> I can't get the validation to work. I'm trying ...
>>>>>>
>>>>>> Data>Validation>Settings> Allow: Custom, Formula:
>>>>>> =MID(WS_Name,2,1) or
>>>>>>
>>>>>> Data>Validation>Settings> Allow: Custom, Formula:
>>>>>> =VALUE(MID(WS_Name,2,1))
>>>>>>
>>>>>> No luck so far. Column C still takes any value. Any ideas?
>>>>>>
>>>>>> TQ, Anny
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

[Biff]

Hi Peo!

Hmmm.....

Yes, unchecking Ignore blanks did allow the shorter formula to work.
However, the long formula:

=C1=--MID(MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32),2,1)

Still contains Indirect calls and does work with Ignore blanks checked.

Biff

"Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
news:[email protected]...
> It's because if you use for instance indirect or offset that refers to
> another cell (in this case I believe it is A1) you need to uncheck ignore
> blanks in the validation window, I think =C1=--MID(WS_Name,2,1)
> will work as validation if you do that.
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Northwest Excel Solutions
>
> www.nwexcelsolutions.com
>
> (remove ^^ from email address)
>
> Portland, Oregon
>
>
>
>
> "Biff" <[email protected]> wrote in message
> news:%[email protected]...
>> OK...........
>>
>> I don't know why:
>>
>> =C1=--MID(WS_name,2,1)
>>
>> won't work. It works just fine when I test it in cells on the worksheet
>> itself but when applied as the formula in data validation it doesn't
>> work. I suspect it has something to do with the named formula.
>>
>> This WILL work:
>>
>> =C1=--MID(MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32),2,1)
>>
>> Or, put this formula in some out of the way cell like AA1:
>>
>> =--MID(WS_name,2,1)
>>
>> and then refer to that cell:
>>
>> =C1=AA1
>>
>> Side note:
>>
>> =VALUE(MID(WS_Name,2,1))
>> =--MID(WS_Name,2,1)
>>
>> do the exact same thing.
>>
>> Biff
>>
>> "anny" <[email protected]> wrote in message
>> news:[email protected]...
>>> Hi Biff
>>>
>>> Here's the named formula WS_Name you requested
>>>
>>> =MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32)
>>>
>>> It properly identifies the name of the worksheet when placed in a cell.
>>>
>>> Thanks for your efforts
>>> Anny
>>>
>>>
>>> "Biff" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> Post the actual formula for WS_Name so I can do some testing.
>>>>
>>>> Biff
>>>>
>>>> "anny" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> Hello
>>>>>
>>>>> Biff - I couldn't get this to work.
>>>>>
>>>>>> You have to refer to the cell that is being validated:
>>>>>> =C1=MID(WS_Name,2,1)
>>>>>>
>>>>> Also, I'm not sure that the cell needs to be refered to as you've
>>>>> indicated. When I simply use =1 as the validation criteria in
>>>>> column C, it works fine. Also, the named function works properly.
>>>>> When I write =VALUE(MID(WS_Name,2,1) in any cell, I get the desired
>>>>> number. I just can't get this expression to work as a validation
>>>>> criteria.
>>>>>
>>>>> Any ideas out there?
>>>>> Anny
>>>>>
>>>>>
>>>>> "Biff" <[email protected]> wrote in message
>>>>> news:uo%[email protected]...
>>>>>> Hi!
>>>>>>
>>>>>> Well, I'm assuming that your named formula works properly.
>>>>>>
>>>>>> You have to refer to the cell that is being validated:
>>>>>>
>>>>>> =C1=MID(WS_Name,2,1)
>>>>>>
>>>>>> Now, the MID function returns TEXT, so if:
>>>>>>
>>>>>> =MID(WS_Name,2,1)
>>>>>>
>>>>>>
>>>>>> Returned 6, that 6 is TEXT and if the user entered a numeric 6 in
>>>>>> cell C1 = rejected!
>>>>>>
>>>>>> So, try this:
>>>>>>
>>>>>> =C1=--MID(WS_Name,2,1)
>>>>>>
>>>>>> Or, format the target cell as TEXT (don't know if you really want to
>>>>>> do that, though!)
>>>>>>
>>>>>> Biff
>>>>>>
>>>>>> "anny" <[email protected]> wrote in message
>>>>>> news:[email protected]...
>>>>>>> hi gurus
>>>>>>>
>>>>>>> I have defined a Name for a function, WS_Name, that returns the name
>>>>>>> of the active worksheet (eg A6-X, A4-R, B7-Q ...etc).
>>>>>>>
>>>>>>> On each worksheet, I want to add validation to column C. The value
>>>>>>> in colum C must be =MID(WS_Name,2,1). In the 3 sheets mentioned
>>>>>>> above, column C should accept ONLY values of 6, 4 and 7
>>>>>>> respectively.
>>>>>>>
>>>>>>> I can't get the validation to work. I'm trying ...
>>>>>>>
>>>>>>> Data>Validation>Settings> Allow: Custom, Formula:
>>>>>>> =MID(WS_Name,2,1) or
>>>>>>>
>>>>>>> Data>Validation>Settings> Allow: Custom, Formula:
>>>>>>> =VALUE(MID(WS_Name,2,1))
>>>>>>>
>>>>>>> No luck so far. Column C still takes any value. Any ideas?
>>>>>>>
>>>>>>> TQ, Anny
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>