Share your suggestions and ideas. Vote on ideas you find valuable.
Do you want to play a crucial role in shaping Jet Global products?
The formulas doesn't work if you delete the latest inserted version of start and end date.
See first comment. I apologize for the length.
I am afraid that it may be slightly confusing how the Fast Financials tool creates and uses the Start and End Date named ranges. As you know, the first time you drop a G/L account from the FF window, it creates the Start and End date options and named ranges (Start_Date and End_Date) on the Options worksheet. At the same time, it references these options above the Amount column and are used in the generated G/L functions. Once the named ranges have been created, they are not removed or changed in any way with subsequent adds of data.
What is not obvious, is that the first add of data creates additional named ranges that are used in the GL functions in the Amount column (Amount_Start_Date, Amount_End_Date). These named ranges use mixed references - eg, Sheet1!E$5 - as opposed to the default absolute reference typical of named ranges. This allows the generated G/L function to be copied across columns to reference additional time periods in the assumed relative start and end date rows above the function. You can see how this is used in the attached sample report.
If you were to add accounts and amounts in adjacent columns rather than below the initial add, the generated GL functions still reference these named ranges. However, due to the relative nature, the GL functions need information in the relative start and end date cells above the new Amount column. See attached example (relativenamedrange.xlsx). The alternative is to modify the GL functions to reference different cells or named ranges.
We believe this is the appropriate approach for the more likely scenario of users building out additional columns with different time periods, not different accounts with the same time period. As you can see, however, it is very difficult to communicate how this works.
Does this make sense - how it works and our reasoning?
I would love to know more about the types of reports you are trying to create using Fast Financials. For an offline discussion, you are welcome to contact me directly at firstname.lastname@example.org.
You won't be notified about changes to this idea.