Master List

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

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
 
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
 
Put this in B1 and copy down..........

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

Vaya con Dios,
Chuck, CABGx3
 
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
 
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
 
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
 
Back
Top