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.
Example:
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?
Best Answer
I just experienced the same issue and the problem seemed to be surrounding date formatting. The spreadsheet settings (file/spreadsheet settings) locale was set to United States and I'm in Canada. When I changed the locale to Canada, my sheet threw up the exact same error pattern as I saw in the final stage of the print process. I had to change a few cells that pulled date information (they were what were causing errors throughout the rest of the sheet) - specifically ones using the date() function. Once that was cleaned up, everything printed flawlessly.