Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? -- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validate Entry Custome Formula and Data Names
Sure. Let's look at it. I also hate it when a work-around is necessary
because a "should have worked" solution is not working. But, usually when I dig into one of these, it ends up being operator error. EVO "T. Valko" wrote: 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? -- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validate Entry Custome Formula and Data Names
OK, you can send the file to me at:
xl can help at comcast dot net Remove "can" and change the obvious. Include a detailed explanation of where everything is and what you tried that didn't work. If the file is big zip it. My email has problems with attachments 1mb. -- Biff Microsoft Excel MVP "EVO" wrote in message ... Sure. Let's look at it. I also hate it when a work-around is necessary because a "should have worked" solution is not working. But, usually when I dig into one of these, it ends up being operator error. EVO "T. Valko" wrote: 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? -- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validate Entry Custome Formula and Data Names
does this work?
if you validate data in cell with a custom sumif formula "= sumif(range,"text",sumrange)" plus a sum(sumrange) Then if you check the €œCircle Invalid Data" button The trouble is you have to manually check the"Circle Invalid Data" every time you save the file or refresh the circles vanish, for some reason it does not automatically appear when data becomes invalid - I don't know why And the circles disappear again when you refresh or save the file so guess it is necessary/useful to check sheet every now and then by clicking on the "Circle Invalid Data" button I do not know why they don't automatically appear when set but you cannot adjust the sum or create a new sum in the cell if the required data/text is missing Maybe the same reason the fill series does not work for weekdays even thought the option is so tantalisingly placed for our choice. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validate date entry in a text box. | Excel Discussion (Misc queries) | |||
validate duplicate entry in a column | Excel Discussion (Misc queries) | |||
Validate Email address entry | Excel Discussion (Misc queries) | |||
Validate in WS to prevent dual data entry | Excel Worksheet Functions | |||
Validate MsgBox Entry to Data in Cells | Excel Discussion (Misc queries) |