--In Sheet2; in a unused Column Row1 (say cell I1) enter the below formula.
This will list all invoice numbers from Sheet1....without blanks

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=IF(COUNTA(Sheet1!$A$1:$A$1000)=ROW(A1),
INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(Sheet1!$A$1:$A$ 1000<"",
ROW(Sheet1!$A$1:$A$1000)),ROW(A1))),"")

--In DataValidationListSource use the below formula

=OFFSET($I$1,,,SUMPRODUCT(--(I1:I1000<"")))

--
Jacob (MVP - Excel)


"Simon" wrote:

Hi,
Please help...
I have a list spreadsheet with invoice details on it. SHEET 1 - Column A
lists invoice numbers. Column B lists individual items on the invoice (i.e.
parts sold). Further information in column C to F (invoice date, amounts,
etc)
On SHEET 2, I want to create a validation list which you can use to select
invoice numbers. Once an invoice number is selected, vlookup will be used to
pull through information in column C to F.
I know how to use vlookup, and to create custom lists so there is no problem
there.
The problem I do have is column A has blanks in it (i.e. one invoice number
to many parts sold). When I try to use Data Validation, it shows the blanks
in the drop-down. I would like to know if there is any way of removing those
blanks, so that only the invoice numbers pull through to the data validation.
Thanks in advance for your help...
Simon