Home |
Search |
Today's Posts |
#1
|
|||
|
|||
vba macro required ?
Hi there,
I have a spreadsheet that someone spent a lot of time on, in getting data presented exactly how they wanted it. A report I have from Crystal will export the same information to a spreadsheet, but guess what: not as presentable as the historical spreadsheet. Question: I want to update the historical spreadsheet with the information from the spreadsheet exported from Crystal ? This can be done manually (yawn). I need a macro that will search the spreadsheet generated with Crystal, first for a customer number: there are 70 customers. Then for each customer there are 42 products. Finally: populate the product count against the correct product number on the historical spreadsheet for each of the 42 products per correct customer number. Any help appreciated. Peter |
#2
|
|||
|
|||
If you can easily get your output in the form of a database query that
delivers a table with columns for customer number, product number and product count then you can make a pivot table based on this query with customer number as row field, product number as column field and product count as data field. Then in the cells which display the data you can use a GETPIVOTDATA worksheet function to extract the appropriate value from the pivot table. Excel 2002 and later would allow a function like =GETPIVOTDATA("Sum of Product Count",PivotSheet!A3,"Customer Number",X2,"Product Number",A4) Where the pivot table is at A3 on sheet PivotSheet, the customer number you want is in X2 and the Product Number is in A4. Before Excel 2002 the function had a different set of arguments which were harder to construct. Hope you have 2002! Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) | |||
Macro Formula revision? | Excel Worksheet Functions | |||
Macro for multiple charts | Excel Worksheet Functions |