Copy Selective Data from Database to Invoice, Based on Certain Criteria
- by Scott
For starters, here is an example of a microsoft excel database I am working with:
Month/Address/Name/Description/Amount
January/123 Street/Fred/Painting/100
January/456 Avenue/Scott/Flooring/400
January/789 Road/Scott/Plumbing/100
February/123 Street/Fred/Flooring/600
February/246 Lane/Fred/Electrical/300
March/789 Road/Scott/Drywall/150
What I want to be able to do is selectively copy info from this databse to invoices (also excel). The invoice has three columns: Address/Description/Amount. I want to be able to automatically fill the invoices in as the database is filled in (either automatically, or if I have to actually manually run the macro to do it, that might be fine).
Each name (Scott, Fred, etc.) will have their own set of 12 invoices for the year. So, e.g., I want to be able to produce a January invoice for all work done for Scott in January, showing the address, the description and the amount, line by line. So every time work on Scott's address(es) is done, the database is filled in, and i want it to "send" that information to the invoice on the next available line, filling in only the Address/Description/Amount columns from the database. Fred's invoice should fill in as any work is done on Fred's addresses. And once the month changes, the next invoice should start filling in.
So first I need to filter the data by the month and the name (and there is actually one more column to filter by, but let's keep this example simpler). Then I need to list the remaining data on the invoice, but only certain cells from the rows that are now left.
Help anyone?
