Define criteria base Name Range

[inddon]

Hello There,

I have 2 worksheets:
1. Invoices - which contains invoice numbers starting from 10.... and 75....
There might be null values in this column as well.
2. Result - which would display unique invoice numbers starting from 10... only

Attached a sample workbook.

How can this be done using named range? Appreciate your help.

Thanks & regards,
Don

 

[NARAYANK991]

Hi ,

I am not clear on what you want ; the standard formula for getting a list of invoices starting with 10 , would be :

=IFERROR(INDEX(Invoices!$A$2:$A$2000,SMALL(IF(LEFT(Invoices!$A$2:$A$2000,2) ="10", ROW(Invoices!$A$2:$A$2000) - MIN(ROW(Invoices!$A$2:$A$2000)) + 1),ROW(A1))),"")

This would be an array formula , to be entered using CTRL SHIFT ENTER.

Instead of using the static reference Invoices!$A$2:$A$2000 , if you wish to use a named range for the list of invoices , you can define a named range called InvoiceList , and in the Refers To box enter the formula :

=Invoices!$A$2:INDEX(Invoices!$A:$A,MATCH(9999999999,Invoices!$A:$A))

where the 9999999999 is assumed to be bigger than any valid invoice number.

Narayan