Hello,
Is there a way to have a template that i have made automatically update files created from it? These edits will not be the data itself, but the equations and styling of the sheets.
For example, tracking expenses, I’ll have a list of purchases for the month and a section on that sheet that sums them by expense type. If i end up adding a new expense type id like to just add it to the template and have it show up in all files created from said template.
In short, no.
.
But you may be envisioning things backwards. If you want the effect you are seeking, you need to have a master spreadsheet document that imports all of the data from the other sheets but does the processing/sorting/filtering itself. Then you are always editing the master spreadsheet for categories, functions, macros, etc.
This is advanced stuff.
One way is to use Data Ranges. They are a little mysterious, but @Villeroy has a synopsis at Import csv into existing sheet/tab. You import your data spreadsheets into a Database that is just used as a data broker to register the data. Then you define data ranges in your master spreadsheet using the data viewer.
Another way is to use INDIRECT() and point to external sheets. This can be very dynamic, but definitely at a performance cost. Here is a slice-of-life example of a Named Function that grabs data from a CSV and brings it into the master document that then “processes” the CSV data:
IF(
ISBLANK(
INDIRECT(
'"''"&INDIRECT(
FixedExternalPathAddr
)&'"''"&'"#$'"&INDIRECT(
FixedExternalSheetAddr
)&'".'"&ADDRESS(
ROW(
)-MATCH(
KeyMoniker,
FixedKeyColumn,
0
)+1,
COLUMN(
)-COLUMN(
FixedKeyColumnAddr
)
)
)
),
'"'",
INDIRECT(
'"''"&INDIRECT(
FixedExternalPathAddr
)&'"''"&'"#$'"&INDIRECT(
FixedExternalSheetAddr
)&'".'"&ADDRESS(
ROW(
)-MATCH(
KeyMoniker,
FixedKeyColumn,
0
)+1,
COLUMN(
)-COLUMN(
FixedKeyColumnAddr
)
)
)
)
[Quotes are doubled above as part of the pretty printing. The ProperCase items are Named Ranges.]
If this code has the Named Function name FetchExternal, it will be used as =FetchExternal in a cell, and that cell will replicate the external source at the same cell, given the referenced offsets.
.
Note: If you directly use a database like Base instead of a spreadsheet document, then you can implement lookup tables. When you update items in the lookup tables those updates will be reflected everywhere. For example, if you use a lookup table with Fee, Fie, Foe in it to populate a droplist in a Base form, then you add Fumm, now anytime you use the droplist it will include Fumm. You can link that to behavior rather than just data entry through macro programming and/or concatenating SQL.
You can check, if DDE() works for you. Around 10 years ago I used this to update data in Writer-files from a Calc-Datasource, but it should work also from Calc to Calc.
https://wiki.documentfoundation.org/Documentation/Calc_Functions/DDE