vba macro required ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter O'Leary - 12 Apr 2005 17:34 GMT

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

Reply to this Message

Bill Manville - 14 Apr 2005 23:49 GMT

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 to this Message