Master List
 

Dear NG:

Is there any way to have Excel check numbers being entered into a range
against a master list of numbers.

Lets say the master list is a named range MasterList (=Sheet1!$H$1:$H$10)
consisting of 10 sequential numbers from 70 thru 79.

Any data entered into named range PartNumberList (=Sheet1!$A$1:$A$24) must
be checked against the master list.

Any number entered into PartNumberList that IS listed in MasterList is just
displayed normally as the number itself.

Any number entered into PartNumberList that is NOT in MasterList is
displayed as the number itself but ammended with "Invalid"

I'm sensing some sort of convoluted IF statement.

Thanks for any help with this.

-Kevin

Master List
 

This may be easier than you think....

See the Data Validation section of Debra Dalgleish's website for
instructions and examples:
http://www.contextures.com/xlDataVal01.html

Is that something you can work with?
(Post back if you have more questions)
***********
Regards,
Ron

XL2003, WinXP


"Kevin" wrote:

Dear NG:

Is there any way to have Excel check numbers being entered into a range
against a master list of numbers.

Lets say the master list is a named range MasterList (=Sheet1!$H$1:$H$10)
consisting of 10 sequential numbers from 70 thru 79.

Any data entered into named range PartNumberList (=Sheet1!$A$1:$A$24) must
be checked against the master list.

Any number entered into PartNumberList that IS listed in MasterList is just
displayed normally as the number itself.

Any number entered into PartNumberList that is NOT in MasterList is
displayed as the number itself but ammended with "Invalid"

I'm sensing some sort of convoluted IF statement.

Thanks for any help with this.

-Kevin

Master List
 

Amended where? If you mean that if you type in a number and in the same cell
you would get the number and a text string saying "Invalid". Then you would
need an event macro. You would be better off using a help column where it
would say "Invalid" in the adjacent cell

=IF(A2="","",IF(ISNUMBER(MATCH(A2,MasterList,0))," ","Invalid"))

as an example where A2 holds the number you want to test, copy down the
formula


--
Regards,

Peo Sjoblom





"Kevin" wrote in message
...
Dear NG:

Is there any way to have Excel check numbers being entered into a range
against a master list of numbers.

Lets say the master list is a named range MasterList (=Sheet1!$H$1:$H$10)
consisting of 10 sequential numbers from 70 thru 79.

Any data entered into named range PartNumberList (=Sheet1!$A$1:$A$24) must
be checked against the master list.

Any number entered into PartNumberList that IS listed in MasterList is
just displayed normally as the number itself.

Any number entered into PartNumberList that is NOT in MasterList is
displayed as the number itself but ammended with "Invalid"

I'm sensing some sort of convoluted IF statement.

Thanks for any help with this.

-Kevin

Master List
 

Put this in B1 and copy down..........

=IF(ISNA(VLOOKUP(A1,H1:H10,1,FALSE)),A1&" invalid",A1)

Vaya con Dios,
Chuck, CABGx3




"Kevin" wrote:

Dear NG:

Is there any way to have Excel check numbers being entered into a range
against a master list of numbers.

Lets say the master list is a named range MasterList (=Sheet1!$H$1:$H$10)
consisting of 10 sequential numbers from 70 thru 79.

Any data entered into named range PartNumberList (=Sheet1!$A$1:$A$24) must
be checked against the master list.

Any number entered into PartNumberList that IS listed in MasterList is just
displayed normally as the number itself.

Any number entered into PartNumberList that is NOT in MasterList is
displayed as the number itself but ammended with "Invalid"

I'm sensing some sort of convoluted IF statement.

Thanks for any help with this.

-Kevin

Master List
 

You can do this with Data | Validation. This pops up a panel in 3
parts - you can specify a message (comment) to display when the cell
is selected, you can specify what values are acceptable (eg your
MasterList), and you can also specify what to do in the event of
invalid data being entered. No need for a convoluted IF statement !!

Hope this helps.

Pete

On Aug 7, 3:44 pm, "Kevin" wrote:
Dear NG:

Is there any way to have Excel check numbers being entered into a range
against a master list of numbers.

Lets say the master list is a named range MasterList (=Sheet1!$H$1:$H$10)
consisting of 10 sequential numbers from 70 thru 79.

Any data entered into named range PartNumberList (=Sheet1!$A$1:$A$24) must
be checked against the master list.

Any number entered into PartNumberList that IS listed in MasterList is just
displayed normally as the number itself.

Any number entered into PartNumberList that is NOT in MasterList is
displayed as the number itself but ammended with "Invalid"

I'm sensing some sort of convoluted IF statement.

Thanks for any help with this.

-Kevin

Master List
 

Although not what you asked for, you may want to consider this alternative
approach. Instead of flagging invalid numbers with the word "Invalid", what
about if you highlight invalid entries by shading the cell it is in... or,
perhaps, changing the color of its text. Give this a try... highlight the
range of cells A1:A24 and click on...

Format/Conditional Formatting

in Excel's menu. Select "Formula Is" in the first drop-down box and paste
this formula...

=AND(NOT(COUNTIF(MasterList,A1)),A1<"")

in the next field. Now, click the Format button and pick a method of display
you want to see for your invalid values. For example, to turn the text for
invalid entries red, click the Font tab and pick a red color from the Color
drop down. (You can experiment with the other possible cell formats in case
you don't like the text color option.) Okay your way back to the
spreadsheet. Now, enter valid and invalid entries in the A1:A24 to see the
effect.

Rick


"Kevin" wrote in message
...
Dear NG:

Is there any way to have Excel check numbers being entered into a range
against a master list of numbers.

Lets say the master list is a named range MasterList (=Sheet1!$H$1:$H$10)
consisting of 10 sequential numbers from 70 thru 79.

Any data entered into named range PartNumberList (=Sheet1!$A$1:$A$24) must
be checked against the master list.

Any number entered into PartNumberList that IS listed in MasterList is
just displayed normally as the number itself.

Any number entered into PartNumberList that is NOT in MasterList is
displayed as the number itself but ammended with "Invalid"

I'm sensing some sort of convoluted IF statement.

Thanks for any help with this.

-Kevin

Master List
 

Many thanks to: Ron Coderre, Peo Sjoblom, CLR Pete_UK and Rick Rothstein
(MVP-VB)
I'm trying out ALL of your suggestions.
I've obviously come to the right place.
Thanks again.
-Kevin

"Rick Rothstein (MVP - VB)" wrote in
message ...
Although not what you asked for, you may want to consider this alternative
approach. Instead of flagging invalid numbers with the word "Invalid",
what about if you highlight invalid entries by shading the cell it is
in... or, perhaps, changing the color of its text. Give this a try...
highlight the range of cells A1:A24 and click on...

Format/Conditional Formatting

in Excel's menu. Select "Formula Is" in the first drop-down box and paste
this formula...

=AND(NOT(COUNTIF(MasterList,A1)),A1<"")

in the next field. Now, click the Format button and pick a method of
display you want to see for your invalid values. For example, to turn the
text for invalid entries red, click the Font tab and pick a red color from
the Color drop down. (You can experiment with the other possible cell
formats in case you don't like the text color option.) Okay your way back
to the spreadsheet. Now, enter valid and invalid entries in the A1:A24 to
see the effect.

Rick


"Kevin" wrote in message
...
Dear NG:

Is there any way to have Excel check numbers being entered into a range
against a master list of numbers.

Lets say the master list is a named range MasterList (=Sheet1!$H$1:$H$10)
consisting of 10 sequential numbers from 70 thru 79.

Any data entered into named range PartNumberList (=Sheet1!$A$1:$A$24)
must be checked against the master list.

Any number entered into PartNumberList that IS listed in MasterList is
just displayed normally as the number itself.

Any number entered into PartNumberList that is NOT in MasterList is
displayed as the number itself but ammended with "Invalid"

I'm sensing some sort of convoluted IF statement.

Thanks for any help with this.

-Kevin