Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
EVO EVO is offline
external usenet poster
 
Posts: 5
Default Validate Entry Custome Formula and Data Names

I am trying to validate an entry based upon it NOT being on a list. (The
entry is a part number and the list is a table of currently out-of-stock
items, so I want to present an appropriate warning message).

Here is the validation custom formula:
=IF(ISERROR(VLOOKUP(Q2,A1001:A1100,1,FALSE)),TRUE, FALSE)

This works fine, but when the cell is copied down the column, the value Q2
(the input cell itself) changes as we would expect and hope. However, so does
the lookup range A1001:A1100 which is a disaster.

I tried giving the Out of Stock table a name and using that in place of the
absolute range, but that just does not work. It does not appear that the
custom validation formula can accept a data name. I would prefer this
solutions so that I can put the Out of Stock table in another tab. This is
what I would like:
=IF(ISERROR(VLOOKUP(Q2,OutOfStock,1,FALSE)),TRUE,F ALSE)

Anyone have an idea?
--

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 171
Default Validate Entry Custome Formula and Data Names

I believe the named range should work (and did in my quick test).
As for the first approach, you should change the table range to use an
absolute reference; instead of A1001:A1100, user $A$1001:$A$1100.
The point of an absolute reference is exactly what you're looking for.... it
doesn't change as it gets copied down or across.

"EVO" wrote:

I am trying to validate an entry based upon it NOT being on a list. (The
entry is a part number and the list is a table of currently out-of-stock
items, so I want to present an appropriate warning message).

Here is the validation custom formula:
=IF(ISERROR(VLOOKUP(Q2,A1001:A1100,1,FALSE)),TRUE, FALSE)

This works fine, but when the cell is copied down the column, the value Q2
(the input cell itself) changes as we would expect and hope. However, so does
the lookup range A1001:A1100 which is a disaster.

I tried giving the Out of Stock table a name and using that in place of the
absolute range, but that just does not work. It does not appear that the
custom validation formula can accept a data name. I would prefer this
solutions so that I can put the Out of Stock table in another tab. This is
what I would like:
=IF(ISERROR(VLOOKUP(Q2,OutOfStock,1,FALSE)),TRUE,F ALSE)

Anyone have an idea?
--

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
EVO EVO is offline
external usenet poster
 
Posts: 5
Default Validate Entry Custome Formula and Data Names

Thanks so much... the absolute address works fine. I should have realized
that. I tested the name again and it just won't work. Works fine in a cell.
Copy the exact formula to the Validate custom entry and it won't work. OK,
thanks again.


"bpeltzer" wrote:

I believe the named range should work (and did in my quick test).
As for the first approach, you should change the table range to use an
absolute reference; instead of A1001:A1100, user $A$1001:$A$1100.
The point of an absolute reference is exactly what you're looking for.... it
doesn't change as it gets copied down or across.

"EVO" wrote:

I am trying to validate an entry based upon it NOT being on a list. (The
entry is a part number and the list is a table of currently out-of-stock
items, so I want to present an appropriate warning message).

Here is the validation custom formula:
=IF(ISERROR(VLOOKUP(Q2,A1001:A1100,1,FALSE)),TRUE, FALSE)

This works fine, but when the cell is copied down the column, the value Q2
(the input cell itself) changes as we would expect and hope. However, so does
the lookup range A1001:A1100 which is a disaster.

I tried giving the Out of Stock table a name and using that in place of the
absolute range, but that just does not work. It does not appear that the
custom validation formula can accept a data name. I would prefer this
solutions so that I can put the Out of Stock table in another tab. This is
what I would like:
=IF(ISERROR(VLOOKUP(Q2,OutOfStock,1,FALSE)),TRUE,F ALSE)

Anyone have an idea?
--

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Validate Entry Custome Formula and Data Names

Here is the validation custom formula:
=IF(ISERROR(VLOOKUP(Q2,A1001:A1100,1,FALSE)),TRUE ,FALSE)


You can reduce that to:

=ISNA(VLOOKUP(Q2,A1001:A1100,1,0))

The named range should work:

=ISNA(VLOOKUP(Q2,OutOfStock,1,0))

But, you can reduce that even further to:

=COUNTIF(OutOfStock,Q2)=0


--
Biff
Microsoft Excel MVP


"EVO" wrote in message
...
I am trying to validate an entry based upon it NOT being on a list. (The
entry is a part number and the list is a table of currently out-of-stock
items, so I want to present an appropriate warning message).

Here is the validation custom formula:
=IF(ISERROR(VLOOKUP(Q2,A1001:A1100,1,FALSE)),TRUE, FALSE)

This works fine, but when the cell is copied down the column, the value Q2
(the input cell itself) changes as we would expect and hope. However, so
does
the lookup range A1001:A1100 which is a disaster.

I tried giving the Out of Stock table a name and using that in place of
the
absolute range, but that just does not work. It does not appear that the
custom validation formula can accept a data name. I would prefer this
solutions so that I can put the Out of Stock table in another tab. This
is
what I would like:
=IF(ISERROR(VLOOKUP(Q2,OutOfStock,1,FALSE)),TRUE,F ALSE)

Anyone have an idea?
--



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
EVO EVO is offline
external usenet poster
 
Posts: 5
Default Validate Entry Custome Formula and Data Names

Biff - the Countif is truly clever. A great way to see if something is in a
table. Thanks. I'll use it next time. I have already propagated the vlookup
into 2,400 cells! I am totally stumped as to why the name is not working. I
did a small test on a blank workbook and it works fine. Just won't work in
this one.


"T. Valko" wrote:

Here is the validation custom formula:
=IF(ISERROR(VLOOKUP(Q2,A1001:A1100,1,FALSE)),TRUE ,FALSE)


You can reduce that to:

=ISNA(VLOOKUP(Q2,A1001:A1100,1,0))

The named range should work:

=ISNA(VLOOKUP(Q2,OutOfStock,1,0))

But, you can reduce that even further to:

=COUNTIF(OutOfStock,Q2)=0


--
Biff
Microsoft Excel MVP


"EVO" wrote in message
...
I am trying to validate an entry based upon it NOT being on a list. (The
entry is a part number and the list is a table of currently out-of-stock
items, so I want to present an appropriate warning message).

Here is the validation custom formula:
=IF(ISERROR(VLOOKUP(Q2,A1001:A1100,1,FALSE)),TRUE, FALSE)

This works fine, but when the cell is copied down the column, the value Q2
(the input cell itself) changes as we would expect and hope. However, so
does
the lookup range A1001:A1100 which is a disaster.

I tried giving the Out of Stock table a name and using that in place of
the
absolute range, but that just does not work. It does not appear that the
custom validation formula can accept a data name. I would prefer this
solutions so that I can put the Out of Stock table in another tab. This
is
what I would like:
=IF(ISERROR(VLOOKUP(Q2,OutOfStock,1,FALSE)),TRUE,F ALSE)

Anyone have an idea?
--






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Validate Entry Custome Formula and Data Names

Exactly what happens when you say it's not working with the named range?

Do you get some kind of error message? Is the named range static or is it
dynamic?

--
Biff
Microsoft Excel MVP


"EVO" wrote in message
...
Biff - the Countif is truly clever. A great way to see if something is in
a
table. Thanks. I'll use it next time. I have already propagated the
vlookup
into 2,400 cells! I am totally stumped as to why the name is not working.
I
did a small test on a blank workbook and it works fine. Just won't work in
this one.


"T. Valko" wrote:

Here is the validation custom formula:
=IF(ISERROR(VLOOKUP(Q2,A1001:A1100,1,FALSE)),TRUE ,FALSE)


You can reduce that to:

=ISNA(VLOOKUP(Q2,A1001:A1100,1,0))

The named range should work:

=ISNA(VLOOKUP(Q2,OutOfStock,1,0))

But, you can reduce that even further to:

=COUNTIF(OutOfStock,Q2)=0


--
Biff
Microsoft Excel MVP


"EVO" wrote in message
...
I am trying to validate an entry based upon it NOT being on a list. (The
entry is a part number and the list is a table of currently
out-of-stock
items, so I want to present an appropriate warning message).

Here is the validation custom formula:
=IF(ISERROR(VLOOKUP(Q2,A1001:A1100,1,FALSE)),TRUE, FALSE)

This works fine, but when the cell is copied down the column, the value
Q2
(the input cell itself) changes as we would expect and hope. However,
so
does
the lookup range A1001:A1100 which is a disaster.

I tried giving the Out of Stock table a name and using that in place of
the
absolute range, but that just does not work. It does not appear that
the
custom validation formula can accept a data name. I would prefer this
solutions so that I can put the Out of Stock table in another tab.
This
is
what I would like:
=IF(ISERROR(VLOOKUP(Q2,OutOfStock,1,FALSE)),TRUE,F ALSE)

Anyone have an idea?
--






  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
EVO EVO is offline
external usenet poster
 
Posts: 5
Default Validate Entry Custome Formula and Data Names

No error message. The validation doesn't work. (If the name is in the list it
doesn't give the error alert).

The name is defined just the way any one would be. In this case it is
"=Contacts!$A$1001:$A$1100".

If I do the test in a cell, it works. For instance
"=COUNTIF(OutOfStock,Q2)=0" - your formula - returns TRUE of FALSE depending
on the value of Q2, but it doesn't in the Validation formula of Q2. Yet,
"$A$1001:$A1100" does work.

Go figure.

Not sure what a dynamic range is.


"T. Valko" wrote:

Exactly what happens when you say it's not working with the named range?

Do you get some kind of error message? Is the named range static or is it
dynamic?

--
Biff
Microsoft Excel MVP


"EVO" wrote in message
...
Biff - the Countif is truly clever. A great way to see if something is in
a
table. Thanks. I'll use it next time. I have already propagated the
vlookup
into 2,400 cells! I am totally stumped as to why the name is not working.
I
did a small test on a blank workbook and it works fine. Just won't work in
this one.


"T. Valko" wrote:

Here is the validation custom formula:
=IF(ISERROR(VLOOKUP(Q2,A1001:A1100,1,FALSE)),TRUE ,FALSE)

You can reduce that to:

=ISNA(VLOOKUP(Q2,A1001:A1100,1,0))

The named range should work:

=ISNA(VLOOKUP(Q2,OutOfStock,1,0))

But, you can reduce that even further to:

=COUNTIF(OutOfStock,Q2)=0


--
Biff
Microsoft Excel MVP


"EVO" wrote in message
...
I am trying to validate an entry based upon it NOT being on a list. (The
entry is a part number and the list is a table of currently
out-of-stock
items, so I want to present an appropriate warning message).

Here is the validation custom formula:
=IF(ISERROR(VLOOKUP(Q2,A1001:A1100,1,FALSE)),TRUE, FALSE)

This works fine, but when the cell is copied down the column, the value
Q2
(the input cell itself) changes as we would expect and hope. However,
so
does
the lookup range A1001:A1100 which is a disaster.

I tried giving the Out of Stock table a name and using that in place of
the
absolute range, but that just does not work. It does not appear that
the
custom validation formula can accept a data name. I would prefer this
solutions so that I can put the Out of Stock table in another tab.
This
is
what I would like:
=IF(ISERROR(VLOOKUP(Q2,OutOfStock,1,FALSE)),TRUE,F ALSE)

Anyone have an idea?
--







  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Validate Entry Custome Formula and Data Names

If the validation doesn't work that means that for some reason the formula
is either returning an error or the result of the formula is FALSE provided
the validation was properly applied.

I know for certain that using a named range works but I tested it anyhow and
it did work as I expected.

Using the specific range instead of a named range is no big deal however, I
don't "like it" when something that *should work* doesn't and I like to find
out why! So, if you want to get to the bottom of this I'd be glad to look at
your file and figure it out.

If you want to do that let me know and I'll let you know how to contact me.

--
Biff
Microsoft Excel MVP


"EVO" wrote in message
...
No error message. The validation doesn't work. (If the name is in the list
it
doesn't give the error alert).

The name is defined just the way any one would be. In this case it is
"=Contacts!$A$1001:$A$1100".

If I do the test in a cell, it works. For instance
"=COUNTIF(OutOfStock,Q2)=0" - your formula - returns TRUE of FALSE
depending
on the value of Q2, but it doesn't in the Validation formula of Q2. Yet,
"$A$1001:$A1100" does work.

Go figure.

Not sure what a dynamic range is.


"T. Valko" wrote:

Exactly what happens when you say it's not working with the named range?

Do you get some kind of error message? Is the named range static or is it
dynamic?

--
Biff
Microsoft Excel MVP


"EVO" wrote in message
...
Biff - the Countif is truly clever. A great way to see if something is
in
a
table. Thanks. I'll use it next time. I have already propagated the
vlookup
into 2,400 cells! I am totally stumped as to why the name is not
working.
I
did a small test on a blank workbook and it works fine. Just won't work
in
this one.


"T. Valko" wrote:

Here is the validation custom formula:
=IF(ISERROR(VLOOKUP(Q2,A1001:A1100,1,FALSE)),TRUE ,FALSE)

You can reduce that to:

=ISNA(VLOOKUP(Q2,A1001:A1100,1,0))

The named range should work:

=ISNA(VLOOKUP(Q2,OutOfStock,1,0))

But, you can reduce that even further to:

=COUNTIF(OutOfStock,Q2)=0


--
Biff
Microsoft Excel MVP


"EVO" wrote in message
...
I am trying to validate an entry based upon it NOT being on a list.
(The
entry is a part number and the list is a table of currently
out-of-stock
items, so I want to present an appropriate warning message).

Here is the validation custom formula:
=IF(ISERROR(VLOOKUP(Q2,A1001:A1100,1,FALSE)),TRUE, FALSE)

This works fine, but when the cell is copied down the column, the
value
Q2
(the input cell itself) changes as we would expect and hope.
However,
so
does
the lookup range A1001:A1100 which is a disaster.

I tried giving the Out of Stock table a name and using that in place
of
the
absolute range, but that just does not work. It does not appear
that
the
custom validation formula can accept a data name. I would prefer
this
solutions so that I can put the Out of Stock table in another tab.
This
is
what I would like:
=IF(ISERROR(VLOOKUP(Q2,OutOfStock,1,FALSE)),TRUE,F ALSE)

Anyone have an idea?
--









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Validate date entry in a text box. GoBow777 Excel Discussion (Misc queries) 0 July 30th 08 08:46 PM
validate duplicate entry in a column wangan Excel Discussion (Misc queries) 4 June 5th 07 01:38 PM
Validate Email address entry Rayasiom Excel Discussion (Misc queries) 2 May 31st 07 10:36 AM
Validate in WS to prevent dual data entry Jonah Excel Worksheet Functions 1 March 10th 06 05:22 AM
Validate MsgBox Entry to Data in Cells David Excel Discussion (Misc queries) 13 December 21st 05 10:31 PM


All times are GMT +1. The time now is 08:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"