I have two sheets.
sheet 1 has a list of invoices.
on sheet 2 I just want to list certain invoices.so sheet 1 may be like this:
001 rmb $25.00 1356
002 ght $35.02 4568
003 ght $45.23 5689
004 rmb $12.25 4568
005 bnj $1.25 4568
006 sdr $12.54 4568
007 rmb $65.25 4568
009 bnj $56.54 4568On sheet 2 I need it to pull out all the rmb lines.
001 rmb $25.00 1356
004 rmb $12.25 4568
007 rmb $65.25 4568
I could make sheet 2 in to a list and sort it that way. but what I
need to do is pull the information into sheet 2, it's no good being
able to sort it on sheet 1.
Any Ideas?
Thanks
in Sheet2!A1
=MATCH(quot;rmbquot;,Sheet1!B$1:B$500,0)
in Sheet2!A2
=MATCH(1,INDEX((Sheet1!B$1:B$500=quot;rmbquot;)*(ROW(Sheet 1!B$1:B$500)gt;A1),0),0)
Copy A2 down as far as you need to
in Sheet2!B1
=IF(ISNA($A1),quot;quot;,INDEX(Sheet1!A$1:A$500,$A1))
copy down and across as far as you need to go
tips
1. of course you can type quot;rmbquot; in a seperate cell and point to it.
2. If you adjust the quot;$1quot; in column A, make same adjustment to the formulae
in the rest of the columns.quot;WTGquot; gt; wrote in message
...
gt;I have two sheets.
gt; sheet 1 has a list of invoices.
gt; on sheet 2 I just want to list certain invoices.
gt;
gt;
gt; so sheet 1 may be like this:
gt;
gt; 001 rmb $25.00 1356
gt; 002 ght $35.02 4568
gt; 003 ght $45.23 5689
gt; 004 rmb $12.25 4568
gt; 005 bnj $1.25 4568
gt; 006 sdr $12.54 4568
gt; 007 rmb $65.25 4568
gt; 009 bnj $56.54 4568
gt;
gt;
gt; On sheet 2 I need it to pull out all the rmb lines.
gt;
gt; 001 rmb $25.00 1356
gt; 004 rmb $12.25 4568
gt; 007 rmb $65.25 4568
gt;
gt; I could make sheet 2 in to a list and sort it that way. but what I
gt; need to do is pull the information into sheet 2, it's no good being
gt; able to sort it on sheet 1.
gt;
gt; Any Ideas?
gt;
gt; Thanks
gt;
gt;
gt;
Bob,
Thank You very much .
I'll give it a try right away.
If this does what I need, you've saved me so much time and trouble.
Thanks again.
Wally
On Fri, 10 Feb 2006 17:38:35 -0500, quot;Bob Tarburtonquot;
gt; wrote:
gt;in Sheet2!A1
gt;=MATCH(quot;rmbquot;,Sheet1!B$1:B$500,0)
gt;in Sheet2!A2
gt;=MATCH(1,INDEX((Sheet1!B$1:B$500=quot;rmbquot;)*(ROW(Shee t1!B$1:B$500)gt;A1),0),0)
gt;Copy A2 down as far as you need to
gt;
gt;in Sheet2!B1
gt;=IF(ISNA($A1),quot;quot;,INDEX(Sheet1!A$1:A$500,$A1))
gt;copy down and across as far as you need to go
gt;
gt;tips
gt;1. of course you can type quot;rmbquot; in a seperate cell and point to it.
gt;2. If you adjust the quot;$1quot; in column A, make same adjustment to the formulae
gt;in the rest of the columns.Bob,
Thank You very much .
I'll give it a try right away.
If this does what I need, you've saved me so much time and trouble.
Thanks again.
Wally
On Fri, 10 Feb 2006 17:38:35 -0500, quot;Bob Tarburtonquot;
gt; wrote:
gt;in Sheet2!A1
gt;=MATCH(quot;rmbquot;,Sheet1!B$1:B$500,0)
gt;in Sheet2!A2
gt;=MATCH(1,INDEX((Sheet1!B$1:B$500=quot;rmbquot;)*(ROW(Shee t1!B$1:B$500)gt;A1),0),0)
gt;Copy A2 down as far as you need to
gt;
gt;in Sheet2!B1
gt;=IF(ISNA($A1),quot;quot;,INDEX(Sheet1!A$1:A$500,$A1))
gt;copy down and across as far as you need to go
gt;
gt;tips
gt;1. of course you can type quot;rmbquot; in a seperate cell and point to it.
gt;2. If you adjust the quot;$1quot; in column A, make same adjustment to the formulae
gt;in the rest of the columns.Bob This worked great except for the second formula keeps giving me
the same value as the first. Can you see my error.
And I took your advice about pointing to the customer id.
=MATCH(C3,Invoices!C$7:C$5000,0)
gt;=MATCH(quot;rmbquot;,Sheet1!B$1:B$500,0)=MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)*( ROW(Invoices!C$7:C$5000)gt;A6),0),0)
gt;=MATCH(1,INDEX((Sheet1!B$1:B$500=quot;rmbquot;)*(ROW(Shee t1!B$1:B$500)gt;A1),0),0)Thanks again for the help.
Wally
On Fri, 10 Feb 2006 17:38:35 -0500, quot;Bob Tarburtonquot;
gt; wrote:
gt;in Sheet2!A1
gt;=MATCH(quot;rmbquot;,Sheet1!B$1:B$500,0)
gt;in Sheet2!A2
gt;=MATCH(1,INDEX((Sheet1!B$1:B$500=quot;rmbquot;)*(ROW(Shee t1!B$1:B$500)gt;A1),0),0)
gt;Copy A2 down as far as you need to
gt;
gt;in Sheet2!B1
gt;=IF(ISNA($A1),quot;quot;,INDEX(Sheet1!A$1:A$500,$A1))
gt;copy down and across as far as you need to go
gt;
gt;tips
gt;1. of course you can type quot;rmbquot; in a seperate cell and point to it.
gt;2. If you adjust the quot;$1quot; in column A, make same adjustment to the formulae
gt;in the rest of the columns.
gt;
gt;Can I expand on this formula to include more then one match value?
can I match to customer number between date1 and date2.
so out of my invoice list I can call up all the invoices for one
certain customer from feb. 15 to march 21.
Thanks again for all the help.
Wally
On Tue, 14 Feb 2006 10:15:26 -0500, WTG gt;
wrote:
gt;Bob This worked great except for the second formula keeps giving me
gt;the same value as the first. Can you see my error.
gt;
gt;And I took your advice about pointing to the customer id.
gt;
gt; =MATCH(C3,Invoices!C$7:C$5000,0)
gt;gt;=MATCH(quot;rmbquot;,Sheet1!B$1:B$500,0)
gt;
gt;
gt;=MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)* (ROW(Invoices!C$7:C$5000)gt;A6),0),0)
gt;gt;=MATCH(1,INDEX((Sheet1!B$1:B$500=quot;rmbquot;)*(ROW(She et1!B$1:B$500)gt;A1),0),0)
gt;
gt;
gt;Thanks again for the help.
gt;
gt;Wally
gt;
gt;On Fri, 10 Feb 2006 17:38:35 -0500, quot;Bob Tarburtonquot;
gt; wrote:
gt;
gt;gt;in Sheet2!A1
gt;gt;=MATCH(quot;rmbquot;,Sheet1!B$1:B$500,0)
gt;gt;in Sheet2!A2
gt;gt;=MATCH(1,INDEX((Sheet1!B$1:B$500=quot;rmbquot;)*(ROW(She et1!B$1:B$500)gt;A1),0),0)
gt;gt;Copy A2 down as far as you need to
gt;gt;
gt;gt;in Sheet2!B1
gt;gt;=IF(ISNA($A1),quot;quot;,INDEX(Sheet1!A$1:A$500,$A1))
gt;gt;copy down and across as far as you need to go
gt;gt;
gt;gt;tips
gt;gt;1. of course you can type quot;rmbquot; in a seperate cell and point to it.
gt;gt;2. If you adjust the quot;$1quot; in column A, make same adjustment to the formulae
gt;gt;in the rest of the columns.
gt;gt;
gt;gt;Can I expand on this formula to include more then one match value?
can I match to customer number between date1 and date2.
so out of my invoice list I can call up all the invoices for one
certain customer from feb. 15 to march 21.
Thanks again for all the help.
Wally
On Tue, 14 Feb 2006 10:15:26 -0500, WTG gt;
wrote:
gt;Bob This worked great except for the second formula keeps giving me
gt;the same value as the first. Can you see my error.
gt;
gt;And I took your advice about pointing to the customer id.
gt;
gt; =MATCH(C3,Invoices!C$7:C$5000,0)
gt;gt;=MATCH(quot;rmbquot;,Sheet1!B$1:B$500,0)
gt;
gt;
gt;=MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)* (ROW(Invoices!C$7:C$5000)gt;A6),0),0)
gt;gt;=MATCH(1,INDEX((Sheet1!B$1:B$500=quot;rmbquot;)*(ROW(She et1!B$1:B$500)gt;A1),0),0)
gt;
gt;
gt;Thanks again for the help.
gt;
gt;Wally
gt;
gt;On Fri, 10 Feb 2006 17:38:35 -0500, quot;Bob Tarburtonquot;
gt; wrote:
gt;
gt;gt;in Sheet2!A1
gt;gt;=MATCH(quot;rmbquot;,Sheet1!B$1:B$500,0)
gt;gt;in Sheet2!A2
gt;gt;=MATCH(1,INDEX((Sheet1!B$1:B$500=quot;rmbquot;)*(ROW(She et1!B$1:B$500)gt;A1),0),0)
gt;gt;Copy A2 down as far as you need to
gt;gt;
gt;gt;in Sheet2!B1
gt;gt;=IF(ISNA($A1),quot;quot;,INDEX(Sheet1!A$1:A$500,$A1))
gt;gt;copy down and across as far as you need to go
gt;gt;
gt;gt;tips
gt;gt;1. of course you can type quot;rmbquot; in a seperate cell and point to it.
gt;gt;2. If you adjust the quot;$1quot; in column A, make same adjustment to the formulae
gt;gt;in the rest of the columns.
gt;gt;
gt;gt;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 quot;gt;A6quot; takes into account the 6 rows worth
of headers, such as quot;gt;A6 6quot; or quot;gt;A6 ROW(Invoices!$C$7)-1quot; 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)gt;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$5000gt;=Sheet1!$D$3)*(Invoices!E$7:E$ 5000lt;=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 gt;= and lt;= 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$5000gt;=Sheet1!$D$3)*(Invoices!E$7:E$ 5000lt;=Sheet1!$E$3)*(ROW(Invoices!C$7:C$5000)gt;A6 RO W(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).quot;WTGquot; gt; wrote in message
...
gt; Can I expand on this formula to include more then one match value?
gt;
gt; can I match to customer number between date1 and date2.
gt; so out of my invoice list I can call up all the invoices for one
gt; certain customer from feb. 15 to march 21.
gt;
gt;
gt;gt;Bob This worked great except for the second formula keeps giving me
gt;gt;the same value as the first. Can you see my error.
gt;gt;
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, quot;Bob Tarburtonquot;
gt; wrote:
gt;Your first formula
gt;=MATCH(C3,Invoices!C$7:C$5000,0)
gt;starts at row 7, so returns a 1 if the first instance is in row 7.
gt;In the second formula, you are testing the row of otherwise acceptable
gt;records against A6.
gt;If if the first instance is in row 7, then the row (7) is greater thatn A6
gt;(1).
gt;
gt;Adust your second formula so that quot;gt;A6quot; takes into account the 6 rows worth
gt;of headers, such as quot;gt;A6 6quot; or quot;gt;A6 ROW(Invoices!$C$7)-1quot; to follow the
gt;first row of data if you move it later.
gt;
gt;=MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)* (ROW(Invoices!C$7:C$5000)gt;A6 ROW(Invoices!$C$7)-1),0),0)
gt;
gt;To add additional criteria, you need to use a formula like the second in
gt;place of the first such as
gt;=MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)* (Invoices!E$7:E$5000gt;=Sheet1!$D$3)*(Invoices!E$7:E $5000lt;=Sheet1!$E$3),0),0)
gt;
gt;Where Invoices column E holds the invoice date and Sheet1!D3 and Sheet1!E3
gt;hold your start and end dates (note I used gt;= and lt;= which INCLUDE the start
gt;date and end date).
gt;
gt;The second formula (and down) is the same as the first except you add back
gt;the condition that the row of the next return is greater than the row of the
gt;previous return.
gt;
gt;=MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3)* (Invoices!E$7:E$5000gt;=Sheet1!$D$3)*(Invoices!E$7:E $5000lt;=Sheet1!$E$3)*(ROW(Invoices!C$7:C$5000)gt;A6 R OW(Invoices!$C$7)-1),0),0)
gt;
gt;I tested this only against as much data as you showed in your original post.
gt;Let me know if you encounter any additional problems (and make sure your
gt;dates are not stored as text).
gt;No problem asking another question.
After filing out the form, I would try recording a new macro without
trying to figure out the VBA code (Go to tools/Macro/Record New Macro)
When recording try to use keystrokes rather than mouse.
You might have use Cotrol Arrow down or something like that to get to
bottom row of sheet 10 (VBA might try to always send you to the same
row when you run it again) and you might want to use GoTo comands
(Ctrl G) to navigate sheet 4.
Just copy-Paste, Copy Paste, etc. Once you've done it once, you can
add a button from the forms toolbar adn the macro to it.
You might want to try 1 copy-paste first and see what happens next
time you try it.
If you can't get VBA to go to the next available row, then restate
your question in the excel.programming newsgroup.
If your specific, which cell on sheet 4 goes to which column on sheet
10, someone will probably post the complete code for you.
Good luck
On Thu, 16 Feb 2006 14:20:21 -0500, WTG gt;
wrote:
gt;Thanks Bob, this worked great.
gt;
gt;Can I bother you for another question?
gt;
gt;If I make sheet 4 a customer form. after I enter all the customer
gt;information, how do I insert it in to the first empty row in my
gt;customer list?
gt;
gt;so if I have 25 customers in my customer list on sheet 10.
gt;when I finish filling in the customer in my form on sheet 4 How would
gt;I (with a macro I'm guessing) copy the information into the next empty
gt;row ( row 26 ) and so on for the next customer and so on and so on....
gt;
gt;Thanks for all the help.
gt;
gt;I turn to the news groups when I need help, but I'm not overly
gt;experianced with them. so if I did the wrong thing in asking another
gt;question without starting a new thread (I think that's the right term)
gt;I'm sorry..
gt;
gt;Thanks again
gt;
gt;Wally
gt;
gt;
gt;
gt;On Tue, 14 Feb 2006 12:17:49 -0500, quot;Bob Tarburtonquot;
gt; wrote:
gt;
gt;gt;Your first formula
gt;gt;=MATCH(C3,Invoices!C$7:C$5000,0)
gt;gt;starts at row 7, so returns a 1 if the first instance is in row 7.
gt;gt;In the second formula, you are testing the row of otherwise acceptable
gt;gt;records against A6.
gt;gt;If if the first instance is in row 7, then the row (7) is greater thatn A6
gt;gt;(1).
gt;gt;
gt;gt;Adust your second formula so that quot;gt;A6quot; takes into account the 6 rows worth
gt;gt;of headers, such as quot;gt;A6 6quot; or quot;gt;A6 ROW(Invoices!$C$7)-1quot; to follow the
gt;gt;first row of data if you move it later.
gt;gt;
gt;gt;=MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3) *(ROW(Invoices!C$7:C$5000)gt;A6 ROW(Invoices!$C$7)-1),0),0)
gt;gt;
gt;gt;To add additional criteria, you need to use a formula like the second in
gt;gt;place of the first such as
gt;gt;=MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3) *(Invoices!E$7:E$5000gt;=Sheet1!$D$3)*(Invoices!E$7: E$5000lt;=Sheet1!$E$3),0),0)
gt;gt;
gt;gt;Where Invoices column E holds the invoice date and Sheet1!D3 and Sheet1!E3
gt;gt;hold your start and end dates (note I used gt;= and lt;= which INCLUDE the start
gt;gt;date and end date).
gt;gt;
gt;gt;The second formula (and down) is the same as the first except you add back
gt;gt;the condition that the row of the next return is greater than the row of the
gt;gt;previous return.
gt;gt;
gt;gt;=MATCH(1,INDEX((Invoices!C$7:C$5000=Sheet1!$C$3) *(Invoices!E$7:E$5000gt;=Sheet1!$D$3)*(Invoices!E$7: E$5000lt;=Sheet1!$E$3)*(ROW(Invoices!C$7:C$5000)gt;A6 ROW(Invoices!$C$7)-1),0),0)
gt;gt;
gt;gt;I tested this only against as much data as you showed in your original post.
gt;gt;Let me know if you encounter any additional problems (and make sure your
gt;gt;dates are not stored as text).
gt;gt;
留言列表
