Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find corresponding values on another worksheet
Hello folks,
We decided to delete all rows on a worksheet that have Invoice Date as today. I have two worksheets with invoices. One has invoice dates and the other one doesn't. However, the same invoices are on both worksheets. For some reason, the other worksheet is not supposed to have Invoice Date and so it does not. My question is: It is easy enough to delete all invoices having today's date from the worksheet that has invoice date but how do I delete those same invoices from the other worksheet that has no Invoice Date? Is there a way to tell Excel to match up invoice numbers from the first worksheet to the second and delete from the second as well? Or maybe I can do some sort of lookup... Please help. Thanks for reading. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find corresponding values on another worksheet
Sounds like you need to flag those invoices on sheet2 (using a new
helper column) to identify corresponding invoices on sheet1; perhaps Inv Number? In sheet2 the new helper column put in: =SUMPRODUCT((Sheet1!$B$2:$B$6=Sheet2!A2)*(Sheet1!$ A$2:$A$6=TODAY())) And copy down to bottom. Any cells that produce "1" (without the ""'s Are your matching invoices; Do an Auto-Filter on this column for the "1"'s Then Delete them; then of course Unfilter and their gone. Now Delete the same Inv's from sheet1. Where B2:B6 are your InvNums on sheet1 and A2 is your Inv Num on Sheet2 And where A2:A6 are your InvDates on Sheet1 Hope that helps, Jim May "Reader" wrote in message oups.com: Hello folks, We decided to delete all rows on a worksheet that have Invoice Date as today. I have two worksheets with invoices. One has invoice dates and the other one doesn't. However, the same invoices are on both worksheets. For some reason, the other worksheet is not supposed to have Invoice Date and so it does not. My question is: It is easy enough to delete all invoices having today's date from the worksheet that has invoice date but how do I delete those same invoices from the other worksheet that has no Invoice Date? Is there a way to tell Excel to match up invoice numbers from the first worksheet to the second and delete from the second as well? Or maybe I can do some sort of lookup... Please help. Thanks for reading. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FIND FORMULA SIMILAR TO VLOOKUP FOR NON-ASCENDING VALUES | Excel Worksheet Functions | |||
Find & Replace within Unlocked Cells in a Protected Worksheet | Excel Discussion (Misc queries) | |||
Comparing a list to a Calendar worksheet. | Excel Worksheet Functions | |||
Find a value in a workbook and return the worksheet name | Excel Discussion (Misc queries) | |||
hide a worksheet so that a macro can still find it | Excel Worksheet Functions |