4

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.

Example:

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(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?

Improve this question
2
  • * 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, 2014 at 17:30
  • 1
    @SeanL: Do you still have this problem?
    – Rubén ♦
    Jul 4, 2015 at 2:45

1 Answer 1

Reset to default
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.