Creating a template that I can reproduce every month by keying ininformation from SAP

[thorton1987]

Hello,

I am trying to navigate through Excel, and one of the issues I'm running into is being able to create a formula template that will save formulas to automatically enter information when importing from SAP.

I have two different worksheets and am linking the imported sheet to a cover sheet I've created in Excel. The easy part was summing the different parts of each category that link perfectly. The problem is that when I sort theimported sheet, it will scramble the order, and my summations in the coversheet will no longer be the same.

I thought this would be easy until I did that.


How should I go about locking the cells in the imported sheet, so that whenI sort from greatest to smallm or smallest to greatest, that the sums in the cover shet DO NOT CHANGE!

I think this is way above my level of expertise on Excel!

Thankyou

 

[GS]

What is 'SAP'? Why is the data scrambled? What, exactly, are you
'importing'?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[thorton1987]

What is 'SAP'? Why is the data scrambled? What, exactly, are you

'importing'?



--

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

Garry,
SAP is a system, from which my company pulls financial data of all kinds. There is an "export to excel" option where it can be sent.

What I am importing from SAP is financial information on monthly reports per category. Once it gets into an excel spreadsheet, I create a summary cover sheet that links to these totals per category.

The order of which the data from SAP is assimilated is nothing that anybodycan control.

Then I'm doing summations of the categories using a formula like this one that most people can do: =sum(A1Sheet2!, A22!Sheet2!)

The problem that I encounter when I use the autofilter for the sheet from SAP, it will change the order of how the data was imported, which means thatthose specific cells that I use the summation formula for, no longer are the ones I need (the summation formulas do not change with the auto filter)

My question is how to force the two to go together, no matter how I sort the data using autofilter.

So the data isn't scrambled per se, it's just reordered in the imported sheet


Hope that's understandable
Thankyou!

 

[GS]

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!


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 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' to gather the data for the intended purpose the
data is being re-assembled for. These are pre-designed templates.

The 'source sheet' 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 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

 

[thorton1987]

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!





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 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' to gather the data for the intended purpose the

data is being re-assembled for. These are pre-designed templates.



The 'source sheet' 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 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.

 

[Claus Busch]

Hi,

Am Tue, 5 Nov 2013 11:39:31 -0800 (PST) schrieb (e-mail address removed):

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.

I don't know which Excel version you use. But there are SUMIF, SUMIFS or
SUMPRODUCT.


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Tue, 5 Nov 2013 20:46:50 +0100 schrieb Claus Busch:

I don't know which Excel version you use. But there are SUMIF, SUMIFS or
SUMPRODUCT.

or you create a Pivot table


Regards
Claus B.

 

[GS]

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.

So what this tells me is that the reports are being generated
'willy-nilly', but I suspect by the same 'department' personnel.
Regardless, you need to urge they use a report template so their's
consistency period-to-period. The important factor here is *staff
productivity*, and so the reort period process needs to be handled in a
pre-defined manner if any degree of reliability/accuracy/consistency is
to be expected/realized.

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.

Sorry, my bad for omitting the lookup functions in my list. Depending
on what I need, I use VLookup/HLookup respectively. I also neglected to
include SumIf(). I see Claus also mentions the newer version SumIfs (I
use early version templates) and SumProduct(). I don't use SumProduct()
but it may also be useful depending on the source data layout.

<FWIW>
I'm not in favor of using a pivot table (per se), unless that's the
layout you want to use for your intended use of the source data. My
clients prefer 'read only' outputs because they usually don't want
users messing around with things as a pivot table would rewuire for
viewing different data. What's common (for my clients, at least) is to
have a sheet with Outlines below a category heading/title so they can
expand/collapse as preferred when they request a single-sheet solution.
This, of course, will print all reports to separate pages as per
inserted PageBreaks, or individual reports as per selection or defined
range. I usually automate this process via a separate addin so printing
prefs are user-friendly. (Using a separate addin leaves the report file
macro-free, which is the usually prefered approach.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Oops.., a typo...

So what this tells me is that the reports are being generated
'willy-nilly', but I suspect by the same 'department' personnel.

Regardless, you need to urge they use a report template so there's

consistency period-to-period. The important factor here is *staff
productivity*, and so the reort period process needs to be handled in
a pre-defined manner if any degree of
reliability/accuracy/consistency is to be expected/realized.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion