Thanks Bob, this worked great.

Can I bother you for another question?

If I make sheet 4 a customer form. after I enter all the customer
information, how do I insert it in to the first empty row in my
customer list?

so if I have 25 customers in my customer list on sheet 10.
when I finish filling in the customer in my form on sheet 4 How would
I (with a macro I'm guessing) copy the information into the next empty
row ( row 26 ) and so on for the next customer and so on and so on....

Thanks for all the help.

I turn to the news groups when I need help, but I'm not overly
experianced with them. so if I did the wrong thing in asking another
question without starting a new thread (I think that's the right term)
I'm sorry..

Thanks again

Wally



On Tue, 14 Feb 2006 12:17:49 -0500, "Bob Tarburton"
wrote:

Your first formula
=MATCH(C3,Invoices!C$7:C$5000,0)
starts at row 7, so returns a 1 if the first instance is in row 7.
In the second formula, you are testing the row of otherwise acceptable
records against A6.
If if the first instance is in row 7, then the row (7) is greater thatn A6
(1).

Adust your second formula so that "A6" takes into account the 6 rows worth
of headers, such as "A6+6" or "A6+ROW(Invoices!$C$7)-1" to follow the
first row of data if you move it later.

=MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)* (ROW(Invoices!C$7:C$5000)A6+ROW(Invoices!$C$7)-1),0),0)

To add additional criteria, you need to use a formula like the second in
place of the first such as
=MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)* (Invoices!E$7:E$5000=Sheet1!$D$3)*(Invoices!E$7:E $5000<=Sheet1!$E$3),0),0)

Where Invoices column E holds the invoice date and Sheet1!D3 and Sheet1!E3
hold your start and end dates (note I used = and <= which INCLUDE the start
date and end date).

The second formula (and down) is the same as the first except you add back
the condition that the row of the next return is greater than the row of the
previous return.

=MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)* (Invoices!E$7:E$5000=Sheet1!$D$3)*(Invoices!E$7:E $5000<=Sheet1!$E$3)*(ROW(Invoices!C$7:C$5000)A6+R OW(Invoices!$C$7)-1),0),0)

I tested this only against as much data as you showed in your original post.
Let me know if you encounter any additional problems (and make sure your
dates are not stored as text).