C
c.e.r.
I have a workbook (.XLS) with 5 worksheets (tabs). This problem
involves two of the worksheets: "Invoice Log | Jan - Mar 2004" and
"Lists & Formulas". The first worksheet (Invoice Log ...) contains
many columns two of which are related to the problem: "Company Name"
and "Invoice Number" (these titles occupy cells C1 and E1
respectively). Cells under "Company Name" are restricted to a list of
companies using Data Validation using the "List" option and the
following source: "=VENDORS2004FIR". The named range is located on
the second worksheet (Lists & ...) and the validation works correctly
(if I enter something not on the list, I receive an error message).
The problem occurs with the "Invoice Number" column. Here, I want to
setup the Data Validation so that when I enter an invoice number the
length of the invoice number (number of characters) is correct. The
length of an invoice number varies between companies and even varies
within companies so that one company may have invoices with numbers 3,
4, 5 characters in length. I created a table in the second worksheet
(Lists & ...) where column one contains all the companies. This
column is the named range referenced above (VENDORS2004FIR). The next
column and those following contain length of invoice numbers for that
company so that one company which is consistent may have the number 5
in column 2, but nothing in the rest of the columns, but another
company many have the number 5 in column 2, the number 6 in column 3,
and the number 9 in column 4. In total, the table contains 6 columns:
the companies and five columns of invoice lengths. This table is
named "INVLG04".
The "Invoice Number" column is formatted as "Text" under the Number
tab under Formatting. The Data Validation formula that I used (for
the first entry in row 2) is as follows:
=OR(LEN(E2)=VLOOKUP(C2,INVLG04,2,FALSE),LEN(E2)=VLOOKUP(C2,INVLG04,3,FALSE),LEN(E2)=VLOOKUP(C2,INVLG04,4,FALSE),LEN(E2)=VLOOKUP(C2,INVLG04,5,FALSE),LEN(E2)=VLOOKUP(C2,INVLG04,6,FALSE))
This is using the Custom option under criteria.
It may be inelegant, but I know it works because when I enter the same
formula in the first worksheet (in a cell anywhere), I get a "TRUE" or
"FALSE" depending on the length of the invoice number I enter in cell
E2.
Despite the formula, if I enter a number in cell E2 that isn't the
correct character length for the company in cell C2, I do not get an
error message.
Is there a reason why this is so? Is there another way to do this. I
don't want to have the table on the active worksheet and would like to
keep it on another worksheet with all the other lists and tables. I
also don't want to create another column in the first worksheet that
evaluates that above formula and is the source for the Data
Validation.
I would appreciate any help in making this work or finding a different
way of accomplishing this.
involves two of the worksheets: "Invoice Log | Jan - Mar 2004" and
"Lists & Formulas". The first worksheet (Invoice Log ...) contains
many columns two of which are related to the problem: "Company Name"
and "Invoice Number" (these titles occupy cells C1 and E1
respectively). Cells under "Company Name" are restricted to a list of
companies using Data Validation using the "List" option and the
following source: "=VENDORS2004FIR". The named range is located on
the second worksheet (Lists & ...) and the validation works correctly
(if I enter something not on the list, I receive an error message).
The problem occurs with the "Invoice Number" column. Here, I want to
setup the Data Validation so that when I enter an invoice number the
length of the invoice number (number of characters) is correct. The
length of an invoice number varies between companies and even varies
within companies so that one company may have invoices with numbers 3,
4, 5 characters in length. I created a table in the second worksheet
(Lists & ...) where column one contains all the companies. This
column is the named range referenced above (VENDORS2004FIR). The next
column and those following contain length of invoice numbers for that
company so that one company which is consistent may have the number 5
in column 2, but nothing in the rest of the columns, but another
company many have the number 5 in column 2, the number 6 in column 3,
and the number 9 in column 4. In total, the table contains 6 columns:
the companies and five columns of invoice lengths. This table is
named "INVLG04".
The "Invoice Number" column is formatted as "Text" under the Number
tab under Formatting. The Data Validation formula that I used (for
the first entry in row 2) is as follows:
=OR(LEN(E2)=VLOOKUP(C2,INVLG04,2,FALSE),LEN(E2)=VLOOKUP(C2,INVLG04,3,FALSE),LEN(E2)=VLOOKUP(C2,INVLG04,4,FALSE),LEN(E2)=VLOOKUP(C2,INVLG04,5,FALSE),LEN(E2)=VLOOKUP(C2,INVLG04,6,FALSE))
This is using the Custom option under criteria.
It may be inelegant, but I know it works because when I enter the same
formula in the first worksheet (in a cell anywhere), I get a "TRUE" or
"FALSE" depending on the length of the invoice number I enter in cell
E2.
Despite the formula, if I enter a number in cell E2 that isn't the
correct character length for the company in cell C2, I do not get an
error message.
Is there a reason why this is so? Is there another way to do this. I
don't want to have the table on the active worksheet and would like to
keep it on another worksheet with all the other lists and tables. I
also don't want to create another column in the first worksheet that
evaluates that above formula and is the source for the Data
Validation.
I would appreciate any help in making this work or finding a different
way of accomplishing this.