sorry, really newbie-question,
i have daily growing data in a big spreadsheet and need some complex evaluations from that, in concrete grouping income and expenses to find the amounts for a tax form,
i can do this by extra columns in calc with formulas in which the values of different classes are first separated towards separate columns and then summed up like in old paper accounting forms, maybe placed on a separate sheet, thus i get a dynamic! evaluation where i can instantly see the effect of changes - e.g. assigning an expense to another account class, that would be fine,
but that would blow up my sheet with the risk of becoming slow, and the formulas would be either plenty or complex or both,
i thought of doing it with base or similar, the evaluation would be separate from my sheet, and the formulas somewhat easier?,
i tried and stumbled into two issues:
-
the connection of base to a calc spreadsheet is more a - static? - ‘import’ than a dynamic connection? i did it by ‘connecting to an existing database - spreadsheet’ in the opening dialog, get a table ‘sheet1’ in base, but when i change something in calc that is not reflected in the table shown in base, thus static? even not after saving (and closing) from calc, and ‘refresh’ in base?
-
in a similar manner the evaluation of the base table by a base ‘query’ is made and then shown - static, it doesn’t react to changes in the ‘table’ until you trigger ‘refresh’ in the query,
question one: is it possible to implement the above setup with dynamic reaction of base query / form / report / chart to changes in the calc sheet?
question two: if not, which would be the ‘best’ approach to achieve the target? in calc, with which formulas, or with other tools?
to illustrate the question:
datasets in rows, columns for date, amount, vat, invoice numbers, indices for sub-account, account, top-account class, text, change in bank, new total in bank, change in cash, new cash total, customer, supplier, and plenty others …
requested: e.g. sum vat-in for accounts a,b,c, sum vat-in for other accounts, sum vat-out for accounts a,b,c, sum vat-out for other accounts, tax relevant earnings (top-account-class e), tax relevant efforts (top-account-class a), in and out for credits (account k) and similar, all grouped to distinct time spans,
from what i know about calc there are plenty possible approaches, as i’m under time stress i’d be very thankful for practical hints,
tia,
b.