Hi all,
I am attempting to build an analysis which references data from a table I built in Table Builder. However, every time I refresh this table my links all break. Is this standard Table Builder behavior? Or is there a setting I'm overlooking that will keep the table name intact for the links I have attaching to it?
The report I'm attempting to build is comparing data from two separate companies in the database, otherwise I'd just use Report Builder to get there…
Thanks in advance,
Nick, completely ready to scrap this project :|
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi Nick -
The Table Builder will automatically assign a name to the Excel table that gets created.
If you reference that table name (e.g., "_Customer")you should have no issues. Referencing cell names (e.g., "Report!$E$7") can be challenging. When a report is run, it is first temporarily placed in Design mode. Thus, any replicated area [such as with an NL(Table) function] is removed. Excel then automatically removes any references to those (now) non-existent cells (replacing the references with #REF).
In addition to the Table Builder (which I love), you also have the option of creating the functions yourself and using the "Company=" filter.
I hope that helps. -
Jet Reports Historic Posts To avoid the breaking of direct references, you can use the excel function INDIRECT.
If you use =INDIRECT("Report!$E$7") instead of =Report!$E$7 as a formula, it will work after refresh too.
But you have to be aware that excel will NOT adjust those formula's if you insert or delete rows or columns!!
rmw
Please sign in to leave a comment.