View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default Infinite sum formula

On May 2, 12:49 pm, wrote:
....
Thank you very much. I am now running into another problem. I
entered the formula per your instructions, but when I click print,
Excel wants to print every single cell that contains the formula,
which is thousands of pages. . . .


I said there were drawbacks to entering such formulas in every row,
but apparently you didn't believe me or expected me to enumerate all
the portential problems. Sorry, didn't think I needed to.

. . I know that I can highlight a selection to print to avoid printing all those pages,
however I am setting up this spreadsheet for someone that is not very familar
with Excel. I am afraid that there will be times she will forget to highlight the
selection and end up printing thousands of pages. Is there a way I can get Excel
to print only the rows that contain actual numbers and not just formulas with no
numbers entered. Does that make sense?


Yes, but if your friend/user EVER does use the File Print Area menu
command, the following workaround will be eliminated. If the worksheet
name were WSN, the range containing these formulas were H6:H65536, and
you wanted columns A through J in the printout, you need to define the
WORKSHEET-level name WSN!Print_Area referring to the formula

=WSN!$A$1:INDEX(WSN!$J$6:$J$65536,MATCH(2,1/(WSN!$H$6:$H$65536<"")))

This sets the WSN worksheet's Print_Area range to the dynamic range
beginning in cell A1 and ending in column J on the last row in col H
that's not equal to "".

To repeat: if your friend/user EVER sets the print area on this
worksheet to anything else, this workaround is fubar, gone, history,
toast, no longer functional. There's no way to prevent this. While
Excel prevents users from changing NORMAL defined names in protected
worksheets, it doesn't prevent users from changing the print area in
protected worksheets.

There are VBA workarounds to restore the dynamic print area formula,
but I'd guess you want to avoid macros for this user.