hiding individual cell values when printing

[Guest]

Is there a way to format individual cells so that the values are visible
normally but don't print? These cells are in the middle of the print area,
so they can't simple be excluded by setting a print area that doesn't cover
these cells.

I can't just hide rows or columns as there are other values that need to be
included in the printout and so can't be hidden. I need a print area with
holes in it.

The long way would be to write a macro that selects all the cells, formats
the cells so that the values don't display, prints worksheet, and then
selects same cells and restores formatting. But this would be a hassle to
update with additional cells.

I was wondering if there was a more elegant way - perhaps an attribute that
can be set in the format cell dialogue box, like the 'Hidden' attribute on
the protection tab for displaying formula?

Thanks in advance,

Glen

 

[Roger Govier]

Hi Glen

Try playing with Format>Cells>Number>Custom> ;;; (that's 3 semicolons)
That will hide what is in the cell, but the values will be there in any
calculation, and will show on the formula bar when the cell is
highlighted.

Be careful with any formulae based upon these cells, as Excel sometimes
tries to be extra "helpful", and the cells with the formula may pick up
the Custom Format. In which case, you need to set the format back on
those cells to what you want.

 

[Guest]

Roger,

thanks for reply. However, when the cell contains a formula, rather than a
simple value, there's no way to see the cell value.

Cheers,

Glen

 

[Roger Govier]

Hi Glen

Not sure what you mean.
If I type a formula into a cell that has been formatted that way, I see
nothing at all in the cell, I see the formula in the formula bar when
the cell is highlighted, and the formula works.

If you are saying you can't see the results of the formula, then with
the cell highlighted, press F9 and the result will show in both cell and
formula bar. Pressing ESC (or Ctrl+Z) returns it to its "hidden" state.

I am using XL2003 and I haven't tested it in earlier versions.

 

[Guest]

Hi Roger,

Not sure what you mean.
If I type a formula into a cell that has been formatted that way, I see
nothing at all in the cell, I see the formula in the formula bar when
the cell is highlighted, and the formula works.

Same at my end.

If you are saying you can't see the results of the formula, then with
the cell highlighted, press F9 and the result will show in both cell and
formula bar. Pressing ESC (or Ctrl+Z) returns it to its "hidden" state.

I tried this but couldn't get the behaviour you describe. On my machine, F9
forces a recalculate of the spreadsheet. I tried all the other F keys, and
with variations of Shift/Ctrl/Alt as well, but couldn't get anything that
toggled the results to display in the formula bar.

I am using XL2003 and I haven't tested it in earlier versions.

Me too.

Having the cell results/contents hidden except in the formula bar isn't
really an ideal solution for me. The cells in question are there for my
benefit as I'm updating the spreadsheet (they're headings etc), and they're
not really much use to me if I have to highlight them before I can see what
they are. I was really hoping for something like the way that buttons and
controls etc can be set so that they display in the spreadhseet but don't
print

eg Autoshape Properties>Properties>Print Object [check box]

Cheers,

Glen

 

[Roger Govier]

Hi Glen

My apologies for poor description. If you place your cursor in the
formula bar, then press F9, Excel will calculate the formula, (or
whatever part of the formula you have highlighted) and show the result.

However, from what you now describe, that is not a solution for you
anyway.

The cells in question are there for my
benefit as I'm updating the spreadsheet (they're headings etc),

eg Autoshape Properties>Properties>Print Object [check box]

I think you have come up with your own solution.
Just use the Drawing toolbar to place a rectangle over the cells in
question. Enter your heading names in the boxes created and right click
the shape, Format Autoshape>Properties> untick the Print box


--
Regards

Roger Govier

Hi Roger,

Not sure what you mean.
If I type a formula into a cell that has been formatted that way, I
see
nothing at all in the cell, I see the formula in the formula bar when
the cell is highlighted, and the formula works.

Same at my end.

If you are saying you can't see the results of the formula, then with
the cell highlighted, press F9 and the result will show in both cell
and
formula bar. Pressing ESC (or Ctrl+Z) returns it to its "hidden"
state.

I tried this but couldn't get the behaviour you describe. On my
machine, F9
forces a recalculate of the spreadsheet. I tried all the other F
keys, and
with variations of Shift/Ctrl/Alt as well, but couldn't get anything
that
toggled the results to display in the formula bar.

I am using XL2003 and I haven't tested it in earlier versions.

Me too.

Having the cell results/contents hidden except in the formula bar
isn't
really an ideal solution for me. The cells in question are there for
my
benefit as I'm updating the spreadsheet (they're headings etc), and
they're
not really much use to me if I have to highlight them before I can see
what
they are. I was really hoping for something like the way that buttons
and
controls etc can be set so that they display in the spreadhseet but
don't
print

eg Autoshape Properties>Properties>Print Object [check box]

Cheers,

Glen

 

[Guest]

Roger,

My apologies for poor description. If you place your cursor in the
formula bar, then press F9, Excel will calculate the formula, (or
whatever part of the formula you have highlighted) and show the result.

Hey, that's cool. I never knew that. Well, that's my new trick for the day.

I think you have come up with your own solution.
Just use the Drawing toolbar to place a rectangle over the cells in
question. Enter your heading names in the boxes created and right click
the shape, Format Autoshape>Properties> untick the Print box

Hmmm. Sometimes it just takes someone else to see the wood for the trees.
That is a kind of obvious work around, although I still think it should be
possible to select particular cells as non-printing.

Thanks for your help,

Glen

 

[Roger Govier]

Hi Glen

You're very welcome. Thanks for the feedback.
Sometimes I can wander around in forests all day<bg>.