EXCEL 2007 - Create Report from accounting system output

Extracting Report from Accounting System outputQuestion: Hoping that I can get some thoughts on how best to pull data for a report from raw accounting system's output.  The attached workbook has two worksheets.

1.  Worksheet named "Data" is the raw output from  our accounting system.  The format is seems difficult to work with I may be missing an obvious solution.  The data shown on the "Data" worksheet is abbrieviated in reality there would be thousands of rows of "Cost Elements" and years of data ( I only include a couple months as an example)

2.  Worksheet named "Report" is the format of the report I'm trying to give our users.

The report needs to total the dollars and hours for each cost element for the periods (months) specified by the user (using drop-down menus).

I'm sort of stuck on how to approach this.  Can I work with the accounting data as is or do I need to reformat the data before I can work with it????
Any suggestions would be much appreciated.

Thank you in advance!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I look at this and it screams make me into a pivot table.  Have you explored that yet?  

The only thing annoying about the data is that the column header is broken into two cells.  This makes it difficult to handle as easy as if they were all in the same cell.

If it was me working on this report I'd concatenate those two cells. to make a single header row and do it as a pivot table.  This will give you more reporting flexability then you would get from VBA and it should be pretty user proof.

dec789Author Commented:
Just started looking at pivot tables.  I've never created a PT from VBA (these reports will need to be created via VBA to be able to respond to different users requirements) so that will be challenging.  Any suggestions on how to get a PT to include/exclude periods (months) as the users pick date range they need to look at???
You will need to work with the .Visible property of the Items in the PivotField. My (VFP) example below shows filtering to a given list.

WITH .pivotfields("Category")
* Reset all to visible (prevents error in filtering selected items)
	PVTITEM.visible = .t.
	IF NOT INLIST(PVTITEM.VALUE,"Value1","Value2") then
		PVTITEM.visible = .f.
ENDWITH	&& pivotfields("category")

Open in new window

CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

This raw output doesn't lend itself to pivot tables very easily - is there any way you can have output as follows:

Period (eg 008/2011)
Units ("Hours", "Dollars")

Then you can construct a PT far more easily.

What is the accounting system and how is the extract created?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dec789Author Commented:

Thanks for your suggestions.  I created a macro that automatically reformats the accounting data (attached).

Can I ask for your help in getting a macro started that will create a pivot table based on the periods a user wants to look at?

In the attached data lets assume the user only wants to look at the months 10, 11, and 12  for 2011.


dec789Author Commented:
Thanks for your help Mike. I appreciate your input.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.