I’m creating a template where one data source enumerates horizontally and one enumerates vertically. I want to create a dynamic formula that expands both horizontally and vertically. I can only find the means to do it in one direction. Is this possible using only a template?
Brian
May be you could use Smart Markers feature (using horizontal parameter to fill data horizontally and dynamically), see the document for your reference.
We are not entirely certain about your requirements, so could you provide more details, sample Excel file and expected Excel file to demonstrate your needs, we will check on how to do it via Aspose.Cells APIs.
In this example, I have 5 Discount tiers and 5 Product tiers. I could have more or less of each. I then want to produce a grid of dynamic formulas that realizes the 25 possible combinations of discounts and products. I would prefer to do this in a template but I don’t see a way to enumerate Smart Markers across both vertical and horizontal axis simultaneously.
|Matrix|Discount|1|2|3|4|5|
|Baseline|| $100.00 | $150.00 | $200.00 | $250.00 | $300.00 |
|A|10%| $90.00 | $135.00 | $180.00 | $225.00 | $270.00 |
|B|20%| $80.00 | $120.00 | $160.00 | $200.00 | $240.00 |
|C|30%| $70.00 | $105.00 | $140.00 | $175.00 | $210.00 |
|D|40%| $60.00 | $90.00 | $120.00 | $150.00 | $180.00 |
|E|50%| $50.00 | $75.00 | $100.00 | $125.00 | $150.00|
Please advise.
MatrixDemo.zip (7.3 KB)
I do not think if there is any better way (other than manual) to cope with it. Even in MS Excel, we do not find such options to automate the process. I guess you may adopt manual way to achieve your tasks. For example, first you will fill kind of constant values/data (without formulas) in the ranges such as “A15:B21” and “C15:G16”. Then you may add dynamic formulas into the respective cells vertically (where you will extend the row number(s)) and horizontally (where you will extend column(s)) accordingly.
OK. If I build the horizontal row with values and formulas, is there a way to get the vertical enumeration to duplicate the cells that the horizontal enumeration has added.
To whit: I put &=Data.Field(horizontal) in B1 with 5 values
I put &=Data.Field2 in A2 and &=&={-1}2*{0}1~(horizontal) in B2.
Row 1 and Row 2 will enumerate horizontally. As Field2 enumerates vertically from A2, is there means to copy the values in columns B:F as Aspose inserts rows?
Brian
There may not be any automatic way for the task. So, you got to do a bit of both (via Smart Markers and manual way). See the attached template file including Smart Markers for your needs. It will cover everything except the dynamic range, i.e., “C17:G21” for dynamic formulas. We think you may try using our shared formulas feature for it, see the document for your reference. Regarding insertion of rows when processing markers in the worksheet, we think you should use Cell.SetSharedFormula to set the formulas after using WorkbookDesigner.Process in code, so markers would be processed and data would be filled into the cells.
Hope, this helps a bit.
MatrixDemo1.zip (8.2 KB)