On Tuesday, November 5, 2013 2:24:16 PM UTC-5, GS wrote:
Ok, thanks for providing more info. Here's how I handle similar
scenarios from different 'system' financial/accounting software apps...
[A]
Firstly, the exported 'reports' are formatted in the original software
to display the data in a specific fashion/order/layout. I don't know of
any (or at least have not seen) such software that does not allow for
creating custom reports, and so I don't buy your claim that you have
"no control" over how the data is laid out in reports!
Even built-in reports are configurable as to what data they show and
how that data is laid out in the report.
That precludes, then, that consistent output is possible for any given
report!
[b]
I have no clue as to why you use AutoFilter! That just buggers up the
report layout so things are harder to find. I leave the report data 'as
exported' so I can design my target sheet[s] to read from the original
report and pull their data based on its default structure "as
exported".
That means my formulas search for keywords that act as 'labels' for the
data I'm after. These 'labels' would be stuff like a category heading
that's *always* found in a particular column after export. That, of
course, means a 'report template' is used to generate the data being
exported so consistency persists month-to-month (or period-to-period).
[C]
I use 'target sheet[s]' to gather the data for the intended purpose the
data is being re-assembled for. These are pre-designed templates.
The 'source sheet[s]' are what the 'system' app exports. Some apps
allow specifying a particular sheet in a particular workbook. Others
just export to a new workbook and name sheets with the system-assigned
'report title'. In this case the target sheets are inserted into this
workbook afterward via the 'Insert Sheets' feature.
In either scenario, the target sheets use their respective source
sheet[s] sheetnames in the formula refs. The formulas use the following
functions in various combinations to pull their data from source
sheets...
Index()
Match()
Offset()
Find()
..to locate data based on the keywords that consistently define the
areas of the exported reports according to the way the reports were
laid out in the system app that generated them.
[Summary]
Now I have a mechanism for generating period reports to Excel that my
pre-designed analysis sheets can reliably pull data from over and over
and over again!
HTH
--
Garry
Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com
Thankyou Garry.
Just a few things:
in your part A), yes there is a way to format the SAP report the way it currently is, but in my position, I do not have the ability to override the current layout.
the reason I was supposed to check via autofilter was that it would show how the foundation of my summations were based on the cell itself and not a text related . I tried each of those formulas, and came up nihil. I might have to redesign my coversheet.
When I did the Vlookup by category, it gave me one of each amount listed, but it didn't give me every number in each category. If there was a way to combine Vlookup with Sum, I think that would work.