software

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 ...

Datagt;Validationgt;Settingsgt; Allow: Custom, Formula: =MID(WS_Name,2,1)
or

Datagt;Validationgt;Settingsgt; Allow: Custom, Formula:
=VALUE(MID(WS_Name,2,1))

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

TQ, Anny

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

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

Biff - I couldn't get this to work.

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

Biff

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

Here's the named formula WS_Name you requested

=MID(CELL(quot;filenamequot;,INDIRECT(quot;A1quot;)),FIND(quot;]quot;,CELL(quot;filenamequot;,INDIRECT(quot;A1quot;))) 1,32)

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

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

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

anny
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

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

Hmmm.....

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

=C1=--MID(MID(CELL(quot;filenamequot;,INDIRECT(quot;A1quot;)),FIND(quot;]quot;,CELL(quot;filenamequot;,INDIRECT(quot;A1quot;))) 1,32),2,1)

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

Biff

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