Workbook: Very large workbook with multiple tabs of data and multiple tabs of reports using sumproduct, query and filter formulas. Tabs

Payroll tab is a reflection of our master database so i'm not dead in the water; however, here's my issue:

Query pulls data from multiple tabs (temple log, killeen log) using conditions to pull specific data. Here's a look: Query Example

There are some hidden columns of data, but Mid, End, Wash, % Bonuses are all calculated fields off of the query data to the left columns. Everything in the screenshot above is showing and calculating correctly. When I print the sheet, that's when things change. Formatting stays the same but some of my cells do not calculate.

Using Google Chrome with built-in PDF viewer. Also tried disabling built in PDF viewer. Chrome: Version 35.0.1916.114

The highlighted field (CPS Bonus) shows a 0 when viewing PDF and sometimes the % Bonuses have not calculated either.


enter image description here

As you see a stark difference.

My opinion is the sheet is calculating but because it takes a moment to pull all the data then calculate, Sheets generates the image for the sheet before the calculates have occurred.

Example query formula pulling data - this works flawlessly:

=if(Q1="","",query(vmerge('Temple Log'!A:Z,'Killeen Log'!A:Z),"select Col3,Col4,Col2,Col8,Col26,Col14,Col6,Col10,Col21,Col22,Col24,Col23 where (Col3='" & Q1 & "' or Col4='" & Q1 & "') and (Col11 is not null) and (upper(Col24)='M' or upper(Col24)='W' or upper(Col24)='E') ",0))

Example formula in CPS Bonus (does not show when PDF is generated):

  IF(countif('Temple Log'!$X$2:$X,"=W")>0,
sum(iferror(filter(CPS!B4:B32*40,CPS!A4:A32=Q1)) - iferror(filter(CPS!D4:D32*40,CPS!A4:A32=Q1),""))))

Example formula in % Bonuses (sometimes these do not show when PDF is generated):

=        if(AND($P$30>0,I3>0),if($E$30 >= $U$7,if(I3*$V$7*E3 >M3,I3*$V$7*E3-M3,"")if($E$30 >= $U$6,if(I3*$V$6*E3 >M3,I3*$V$6*E3-M3,""),"")), "")

Anybody have any ideas for work-arounds?

  • * Does say all changes save before printing. I select a name from Q1, drop down menu, then it loads the vmerge & query. From there I have formulas to the right of query that sometimes don't calculate and I have to delete them and hit undo to force them to calculate. Additionally, those same calculations don't appear when printing. – SeanL Jun 11 '14 at 17:30
  • 1
    @SeanL: Do you still have this problem? – Rubén Jul 4 '15 at 2:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.