Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a master look-up list in Excel? | New Users to Excel | |||
create a master list from other sheets | Excel Worksheet Functions | |||
Reference every 4th row from a master list of modifiable length | Excel Discussion (Misc queries) | |||
Update master list with other lists | Excel Worksheet Functions | |||
Is it possible to setup a self-updating master list? | Excel Discussion (Misc queries) |