Hello,
I have a workbook consisting of 12 pages, January through December. Each
page is designed to track orders. Each page is populated with product order
data in rows 8 through 54, and columns A through U. Columns R, S, and T are
populated when a payment for an order is received.
I'd like to copy the contents of certain cells to another spread sheet
(actually 3 spread sheets) such that the data to be copied is limited to
certain columns in a specific Row based upon specific search criteria.
More to the point:
The goal is to track payments for each month by 3 methods; Check (CHK),
Credit Card (CC), and Electronic Funds Transfer (EFT). The appropriate
abbreviation is entered in Column 'S'. Column 'R' contains the Date Paid.
Column 'A' contains the Order Number, Column 'B' the Customer Name, and
Column 'N' the Amount Due.
Desired result:
Search Column 'S' (Date Paid) to find payments (the Date Paid is in no
particular order) made in January (for each of the three types of payments)
and copy the contents of that Row's intersecting cells with Columns A (Order
Number), B (Customer), N (Amount Due), and R (Date Paid) to the appropriate
'EFT Deposits', 'CC Deposits', or 'Check Deposits' page.
Further, Column 'R' (Date Paid) on the January page will also show payments
received in February, March, and possibly April (late pays). So, April EFT
Deposits will have to look at January, February March and April to find April
Deposits.
Summary:
Search Column 'S' for 1st payment made in January and determine if EFT, CC
or CHK.
In that Row, copy contents cells of intersecting Columns A, B, N, and R to
corresponding payment type sheet.
Repeat without duplication, hopefully in the date order the payment was
received.
There can be payments made on the same date that are EFT, CC, and CHK (or
any combination, i.e., 5 EFT’s, 2 CC’s, 1 CHK).
Existing Format:
Column A: Number (5201)
Column B: Text (James P. Smith)
Column N: Currency ($ 46.50)
Column R: Date (01/01/09)
I've tried VLOOKUP, HLOOKUP, INDEX, MATCH, and ROW but with my limited
knowledge and experience with Excel, I have been unsuccessful in achieving
the goal. Any help or solution would be appreciated. Thanks.
|
Similar Threads
