Excel resizing
When trying to resize tags in Excel,
Templater will choose the best possible range which includes all tags.
Templater has specialized handling for various Excel features, such as:
- tables
- named ranges
- merge cells
- charts, graphs and pie charts
- anchors/links
- formulas
- pivots
- images
- conditional formatting
- PowerQuery/Get & Transform
- newlines, shapes, ...
If you have tags on multiple sheets this means that Templater will choose all sheets between
first and last one.
This is useful for reports which generate mostly the same content
but have different first and/or last page.
Page resizing can be invoked in several more ways. If all tags are on
single sheet and you want to resize that entire sheet, you can place a
single tag in header, or use sheet metadata on one of tags.
For example, if your object has ID property, you can resize entire
sheet with [[ID]:sheet] tag.
If tag is found in the sheet name
(in which case {{TAG}} format must be used)
this will indicate that full sheet resize must be performed.
But sometimes you have nested collection – for example, simple
invoice object has items collection, but if we want to process
multiple invoices at once, then we probably want table for each items
per invoice and some bigger range for invoice data.
Named range
enables us to specify how much room we want per single invoice.
Ranges can be nested, so very complicated reports can be created this way.
If Templater can't find table or named range, it will calculated
maximum range which encapsulates all tags and use this range as context.
Merge cells, named ranges and tables influence the originally selected tag range.
Since Templater will avoid breaking them during resize, they expand the area which is resized.
If same tag is repeated multiple times in a single sheet, both will be included in the resize context.
Builtin special metadata - fixed - requires the use of tables to behave correctly (as expected).
Comments will be duplicated on resize (or removed on resize 0).
Tags can be repeated in different sheets, in which case resizing will behave as if each sheet contains a separate collection which needs to be resized.
Special case of resizing is resizing to 0 = removing context.
Some object types can be removed while others cannot.
If resize(tags, 0) is called on a table, table will still remain in the document, while the first row will look empty.
If resize(tags, 0) is called on named range, it will be removed completely (even the tables within the named range).
For resize to work as expected all tags within named range must be specified, otherwise a different logic will be invoked.
During resize, row/cell styles will be maintained.
This means that row height should be preserved, along with cell formattings.
Both horizontal and vertical resizing will
move objects around
(merge cells, formulas, named ranges, ...) but it will also rewrite formulas to reference expected cell.
Since rewriting formulas
has a performance penalty it's always preferable to write formulas in such a way
that they don't need to be changed when they are moved around.
This can be done by formula using table columns or named ranges as input source instead of referencing a specific cell.
Cell reference will be respected, so that formulas which reference cell in a fixed way will be changed differently
than formulas which reference cell in a sliding way.
Templater is also aware of the PowerQuery/Get & Transform tools and will cope with tags used within them.
This allows for complex scenarios such as embedding of CSV documents with tags bound via
PowerQuery to a sheet.
Template: Tags across multiple rows
Result screenshot:
