First I think that a sheet that needs calculating for 10+ minutes isn't designed well. But that is just my opinion :)
There really isn't any other way to get it done?
Second, to answer your question, Jet has a function to convert formula's to values after running the report.
You have to have 'Auto+Hide+Values' in cell A1.
Every Jet formula will be replaced by its value after the report is run.
But ONLY Jet formula's
And your VLOOKUP's are Excel formula's, so they will still be calculated upon opening.
To avoid this you can add a Jet formula to the Excel formula.
Does your VLOOKUP result in a number or a text?
To add some Jet to a number result try to add '*NL(,NP("Integers",1,1))'. It multiplies the result with 1.
To add it to a text result try to add 'NP("Eval","=""""")'. It will add an empty string.
Of course the initial run will take a bit more time (Jet has to evaluate the formula's) but there is no recalculation on opening.
And last: there are no silly questions :)
rmw
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Hello.
If your goal is to keep your VLookup functions from calculating when you open the report you could embed them into a Jet EVAL function. It would look like this: =NP("Eval","=VLOOKUP(H7,$C$3:$D$7,2,0)"). This would prevent the function from recalculating each time the report is opened and would only update when a refresh is run in Jet. Additionally, if you save this report as +Values (or schedule with values only option) then the report will save without the Jet function and you are left simply with the results of the vlookup until its refreshed.
Hope this helps. -
Jet Reports Historic Posts Hi, I'm fairly new to this whole Jet thing so forgive me if I'm asking silly questions that I probably should already know the answer to.
My question relates to the use of vlookup in a report I have generated.
Basically the jet part of the report runs without any problems, but once it has run, there are a large amount of Vlookups generated (using "rows") when I schedule this report, and open it, excel then starts doing the lookups and it is taking 10 minutes plus to become usable, I can then copy and paste the sheet as data but this takes another 10 minutes to calculate and I lose some of the other minor functionality (subtotals etc)
what I am looking for if it is possible is a Jet function that can do the Vlookups while the report is running and then paste the results as data rather than leave the lookups in place, I have tried using the option for a values only workbook but this only removes the Jet formulae.
is there a simple solution?
again sorry if this sounds like a silly question.
thanks
Please sign in to leave a comment.