Creating templates using Microsoft Excel

Hello All,

We have a system to generate reports out of SAP in the form of excel spreadsheets. My boss wants to use this data to come up with reports with charts on performance.

I took one of the reports, used the data in the reports, created new fields, pivot tables, charts and wrote macros and the report is good. My boss wants to this periodically. I suggested, we get the report periodically from SAP, copy the information and replace it with the old information and "Refresh" the worksheet to change all the pivot tables and charts.

Technically, I am trying to create a template. Is there a way better way to do this then the way that I have been using?

Any help or suggestion is highly appreciated.

Thanks,
booo

Hi boo,

The answer is a definite, it depends. If the data that comes from SAP is all on one sheet and all the reports get generated from that single sheet it would look better, but not a done deal. If the amount of data that came from SAP varies, you may need to reset the range of data for charts and pivot tables to work correctly.

A lot of this question depends also on how smart the programmer was who set up the Excel part was in the first place. Did s/he allow for different amounts of data? Did s/he do it after the data was already added to a single sheet.

Sorry for the above answer, but it really does depend .

Ok. Suppose that there is just one format of report. It will have the same data elements. However as time goes, the number of entries increase and when generated on a periodical basis, that report would have added entries to it.

So all i have to do is to get this report, copy all the data and then paste it in my excel spreadsheet with the functionalities. Infact this is what I do presently. I am asking if there is a better way to do it.

That is certainly the first step. Just paste in the new SAP data and check it like mad. If a simple cut and paste of new data works, you'll be lucky. More than likely, if you paste in more data it won't do reports with all the data, just what it had set on the template.

Also look for named ranges in the template to see if the Excel s/he used them. If so you may be able to reset these ranges to make more SAP data work....

"One test is worth a thousand opinions"

Right. So it works with whatever is available on the template. So All i need now is a solution to make sure that the pivot tables are created based on whatever data is available in the Excel spreadsheet.

If I could get the Pivot tables to reference all data and refresh, then the charts will change based on them and there would not be any further need.

I'd rehire that programmer that made this work. It seems they did things right.

I think you've got it.

Wait. I think I lost you somewhere. What are you talking about?

You lost me, when I said "it depends on how smart the programmer was" . If pulling in SAP data that is a variety of sized still works, you should rehire the programmer who wrote that code.