Thanks!
@Villeroy - you have right (I did not see the calculation error)
Using a database is a very flexible solution.
The layout of the result is very easy to control. (I know)
But... I still have a little hard time in this case to see the benefits.
Last time (many years ago) I started using databases with OO / LO, but then there were so many bugs and weak support that I had to give up.
I have some projects that need to be solved with a database. - but that's another challenge.
The document I am working on is only relevant for a short time.
Actually, all data is completely static - it is only what is to be presented and how it is to be presented that can be changed.
A brief description of the background .:
In this case I have four CSV-files (with the same structure but different content) from two places. (= eight source files)
Now these files are on one page each in a spreadsheet.
In the same spreadsheet there also are four sheets with a brief summary of the other eight sheets. (Now I added another sheet with the table of groups.)
All in one file (its - great).
The source information (in this case) contains between 1 to 500 rows and 10 columns of information.
To make it easier to get an overview of this information, I would like a compilation of suppliers and groups.
(It is with this compilation that I have struggled with - I did in a different way before that were very time consuming)
Thanks @Villeroy for mentioning pilot- / pivot- tables (I was giving up - because I could not get several different tables to work on the same page.) - Now it seams to work.
Back to my idea to solution (right now)
This is now the source. Added two columns
- Amount (Quantity * Price)
- Category Name
(=OM($A14<$E$4;"Invalid"; OM($A14<$F$4;$G$4; OM($A14<$F$5;$G$5; OM($A14<$F$6;$G$6; OM($A14<$F$7;$G$7; OM($A14<$F$8;$G$8; OM($A14<$F$9;$G$9; OM($A14<$F$10;$G$10;"Invalid" )))))))))
- The test information
After that, it became easy to use pivot tables to make desired structure.
- Examples of results with pivot tables
Do not know if it is possible to improve all IF statements to group the different rows - but it works.
(The IF-instruction will be long - about 500 characters)
What now could be improved is to display data from these individual pages to the compilation pages.
Right now I see no other solution than to manually copy data from the pivot tables
- and add information (like different VAT rates), units and do some calculations and maybe a chart.
Managed to find out how GETPIVOTDATA() works. but fails to copy information from a pivot table with unknown contents.
An updated test file is attached.
)
.:
