• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formulas turning to Ref!

Costas

Member
Hi Chandoo,

I’ve set up a Sales summary report with sumifs formulas and links to a Jet reports table. They work fine…well, until I run the Jet Reports refresh which deletes and recreates the table, resulting in all my links turning into refs. Below is a formula before and after refresh.

=SUMIFS(GLEntry[Amount];GLEntry[GL No.];"41000";GLEntry[Month];$B$4;GLEntry[BU];$C5;GLEntry[IG];D$4)

=SUMIFS(#REF!;#REF!;"41000";#REF!;$b$4;#REF!;$C5;#REF!;D$4)

Is there a way I can fix my formulas so they don't get updated during the refresh process? I've already tried switching the calculation to manual but Jet Reports switches it back to Automatic.

I've also tried to Replace all from "=" to " '=", running the refresh and replace back from '= to = but some of the formulas to not revert back and stay as text.

Has any of you come across this before?

Thanks
Costas
 
Hi Costas ,

If you don't have too many formulae , you can have a macro which inserts these formulae into the respective worksheet cells ; this macro can be run after you run the query , so that each time the data is updated , the formulae are reinserted correctly.

Narayan
 
Hi Narayan,

Unfortunately I do and they are not adjacent to each other so it gets a bit messy. I've resorted to putting the indirect function in front of each table reference but it slowed down the processing.

Costas
 
Hi, Costas!

What about this schema?
- point your formulas to range 1
- range 1 points to a second mirrored range 2
- range 2 is that which is updated (deleted, created) at refresh process

Regards!
 
Hi, Costas!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
Back
Top