I have formulae in cells to reference another sheet, but if the referenced
cell is blank, then the new cell I made blank (using the IF function). The
problem is, when I want to print, Excel wants to print all of the cells with
entries in them - even the ones with formulae in them that are blank.

Is there any way to prevent the empty cells from printing?

Clarification:
I have copied the formulae down several hundred rows, because it is a
template that will receive different sized files. So there are several
hundred rows with formulae in them, but they show up blank, and I would like
to avoid printing the blank cells.

If the rows are either empty or contain formulae returning "empty", then hide
them
--
Gary's Student


"dill_weed" wrote:

Clarification:
I have copied the formulae down several hundred rows, because it is a
template that will receive different sized files. So there are several
hundred rows with formulae in them, but they show up blank, and I would like
to avoid printing the blank cells.

dill_weed wrote...
I have copied the formulae down several hundred rows, because it is a
template that will receive different sized files. So there are several
hundred rows with formulae in them, but they show up blank, and I would like
to avoid printing the blank cells.

If all these blank rows will be clustered below the nonblank rows, you
could define the print area for the worksheet using a formula. For
example, if the worksheet were named WS, then run the menu command
Insert Name Define, enter WS!Print_Area in the topmost field in the
Define Name dialog and enter the formula

=OFFSET(WS!$A$1:$J$1,0,0,
LOOKUP(2,1/(WS!$A$2:$A$65536<""),ROW(WS!$A$2:$A$65536)))

in the 'Refers to' entry field, then click OK. Replace the worksheet
name, WS, as needed. Also note that the print range could start in any
cell, so replace the first argument to OFFSET, WS!$A$1:$J$10 in the
formula above, with the range address of the topmost row of your
intended print range, and adjust the LOOKUP call as needed to
correspond to changes in the location of the print range (note that
both ranges in the LOOKUP call begin in the cell immediately below the
top-left cell of the print range).

Harlan Grove Wrote:
dill_weed wrote...
I have copied the formulae down several hundred rows, because it is a
template that will receive different sized files. So there are
several
hundred rows with formulae in them, but they show up blank, and I
would like
to avoid printing the blank cells.

If all these blank rows will be clustered below the nonblank rows, you
could define the print area for the worksheet using a formula. For
example, if the worksheet were named WS, then run the menu command
Insert Name Define, enter WS!Print_Area in the topmost field in
the
Define Name dialog and enter the formula

=OFFSET(WS!$A$1:$J$1,0,0,
LOOKUP(2,1/(WS!$A$2:$A$65536<""),ROW(WS!$A$2:$A$65536)))

in the 'Refers to' entry field, then click OK. Replace the worksheet
name, WS, as needed. Also note that the print range could start in any
cell, so replace the first argument to OFFSET, WS!$A$1:$J$10 in the
formula above, with the range address of the topmost row of your
intended print range, and adjust the LOOKUP call as needed to
correspond to changes in the location of the print range (note that
both ranges in the LOOKUP call begin in the cell immediately below the
top-left cell of the print range).

I must say this is truly wonderful. I've been looking for a way to
dynamically set the print area that is ideal for a long time without
messing with hiding rows and cells and such. This has been working
perfectly on all of my templates so far!

Thanks!


--
Vassago
------------------------------------------------------------------------
Vassago's Profile: http://www.excelforum.com/member.php...o&userid=36906
View this thread: http://www.excelforum.com/showthread...hreadid=484040

What's wrong with using File-Print Area-Set Print Area

"dill_weed" wrote:

Clarification:
I have copied the formulae down several hundred rows, because it is a
template that will receive different sized files. So there are several
hundred rows with formulae in them, but they show up blank, and I would like
to avoid printing the blank cells.

Sloth wrote...
What's wrong with using File-Print Area-Set Print Area

Nothing if you're the sort of person who likes to do everything
manually, but some people prefer that tasks that could be automated are
automated.

exactly - thank you harland. I am creating a template for others to use, and
I want entries to load automatically (referenced into several sheets), and
then be able to print easily - without making the user set a new print area
every for every single file that they create. As it is now, they will either
have to set a new print area every time they want to print a file, or if they
don't they will get several sheets of blank cells (because there are formulas
in them).

"Harlan Grove" wrote:

Sloth wrote...
What's wrong with using File-Print Area-Set Print Area

Nothing if you're the sort of person who likes to do everything
manually, but some people prefer that tasks that could be automated are
automated.

Wow, settle down guys. I meant no offence, and was just offering a
suggestion. I didn't know if you even considered that an option.

"dill_weed" wrote:

exactly - thank you harland. I am creating a template for others to use, and
I want entries to load automatically (referenced into several sheets), and
then be able to print easily - without making the user set a new print area
every for every single file that they create. As it is now, they will either
have to set a new print area every time they want to print a file, or if they
don't they will get several sheets of blank cells (because there are formulas
in them).

"Harlan Grove" wrote:

Sloth wrote...
What's wrong with using File-Print Area-Set Print Area

Nothing if you're the sort of person who likes to do everything
manually, but some people prefer that tasks that could be automated are
automated.