Workbook: Very large workbook with multiple tabs of data and multiple tabs of reports using sumproduct, query and filter formulas.
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:
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.
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(isblank(Q1),"", 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?