Printing Hidden Cells

[Guest]

I have a loan spreadsheet where I have hidden extra rows beyond the term of
the loan. I do not want these hidden rows to print.

Excel has a Loan Amortization spreadsheet that is able to do this. Does
anyone know how they did this?

 

[Guest]

Excel doe not normally print hidden rows or columns, but if it does you could
select the cells you want and select 'selecteion' in the print dialogue.

What part of the Loan Amortization spreadsheet do you not understand?
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

 

[Dave Peterson]

If they're hidden rows, then they won't print.

If you mean that you have formulas that evaluate to "" and don't want those rows
to print until they evaluate to something you can see...

========

Saved from a previous post.

If those formulas appear at the end of the data and you don't want to use
autofilter...

If I can pick out a column indicates if that row is used or not, then I like
this technique:

(I used column A in my sample, but you can use any column you want.)

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<>""),ROW(Sheet1!$A$1:$A$1000))

(Make that 1000 big enough to extend past the last possible row.)

Then once more:
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3)

That last 3 represents the last column to print (A:C in my example).

And change the worksheet (sheet1) if necessary (in all the places).

 

[Guest]

Hi John:

The excel Loan Amoritization spreadsheet goes up to 30 years. The
spreadsheet makes cells invisible through conditional formating for loans
less than 30 years. I am able to do the same thing with my spreadsheet. So I
understand the conditional formatting.

However, the Loan Amoritization Spreadsheet is able to print out only the
visible cells or rows. I can't figure out how this spreadsheet is able to
only print out the visible cells. When I print out my spreadsheet it
includes all the invisible cells or rows (made invisible through conditional
formating) in the spreadsheet along with the visible cells or rows.

I would really like to know how this is done in Excels Loan Amoritization
spreadsheet so I can incorporate it into my custom spreadsheet.

Thanks

GPIE

 

[Guest]

Hi Dave:

The excel Loan Amoritization spreadsheet goes up to 30 years. The
spreadsheet makes cells invisible through conditional formating for loans
less than 30 years. I am able to do the same thing with my spreadsheet. So I
understand the conditional formatting.

However, the Loan Amoritization Spreadsheet is able to print out only the
visible cells or rows. I can't figure out how this spreadsheet is able to
only print out the visible cells. When I print out my spreadsheet it
includes all the invisible cells or rows (made invisible through conditional
formating) in the spreadsheet along with the visible cells or rows.

I would really like to know how this is done in Excels Loan Amoritization
spreadsheet so I can incorporate it into my custom spreadsheet.

Thanks

GPIE

 

[Dave Peterson]

If it's the same workbook that I've used, it uses defined named based on a
column that looks blank if the row is not used and has something in it if the
row is used.

Much like my earlier post showed.

 

[Gord Dibben]

With the Loan Amortization workbook open go to Tools>Protection>Unprotect sheet.

Now Insert>Name>Define to look at the range names.

See how the Print Area is a named range.

Refers To: =OFFSET(Full_Print,0,0,Last_Row)

Then see that LastRow refers to:

=IF(Values_Entered,Header_Row+Number_of_Payments,Header_Row)

And Full_Print refers to: ='Amortization Table'!$A$1:$I$377

You too can do this on your own worksheet.

Just takes a bit of work.


Gord Dibben MS Excel MVP

 

[Guest]

Thanks Gord - I was able to follow your instructions and my spreadsheet now
works just like the Loan Amoritization spreadsheet.

GPIE

 

[Gord Dibben]

Good to hear that.

Was a bit of work, but you stuck at it.


Gord