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!
AcctSysOutput.xlsx
dec789Asked:
Who is Participating?
 
mikeopoloCommented:
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")
Amount

Then you can construct a PT far more easily.

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

Regards
Mike
0
 
ScriptAddictCommented:
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.

-SA
0
 
dec789Author Commented:
SA
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???
0
Cloud Class® Course: CompTIA Cloud+

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

 
mikeopoloCommented:
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)
FOR EACH PVTITEM IN .PIVOTITEMS
	PVTITEM.visible = .t.
NEXT 
FOR EACH PVTITEM IN .PIVOTITEMS
	IF NOT INLIST(PVTITEM.VALUE,"Value1","Value2") then
		PVTITEM.visible = .f.
	endif
NEXT 
ENDWITH	&& pivotfields("category")

Open in new window


Regards
Mike
0
 
dec789Author Commented:
Mike,

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.

Don

NewFmtAcctSysOutput.xlsx
0
 
dec789Author Commented:
Thanks for your help Mike. I appreciate your input.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.