Page Break Preview Automatically Adjusting

  • Thread starter Thread starter FARAZ QURESHI
  • Start date Start date
F

FARAZ QURESHI

Upon using the general template of Loan Ammortization I came across a new
finding and found it quite interesting.

The sheet has builtin formulae spread over 377 rows but for example if I
insert a loan period of 2 years only the relevant part of 24 months is set as
printable area and upon changing it to 3 years the area of 36 months is
automatically set for and vice versa.

How could that be achieved?
 
Insert>Name>Define.

Last_Row refers to:=IF(Values_Entered,Header_Row+Number_of_Payments,Header_Row)

See the ranges for each of those names.

Works like a Dynamic Range

Header_row is row 18 and 1st payment starts on row 19.

If there are values entered, it computes the number of rows to use based upon
the number of payments, which is based upon years and number of payments per
year.

Number of payments is Years * Months so for 2 years at 12 months you would get
24 rows which is 19:42

3 years at 12 months = 36 rows


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Printing edge to edge and automatically generating a new page query 3
automatically adjust charts 6
Finding automatic page breaks in new, blank worksheet 1
automatic page breaks 4
finding and reporting in column A, where a series of column reaches zero 2
Generating automatic entries (holidays) for Employee AttendanceDatabase 1
Setting Prnt Areas & Page Breaks 5
find last entry and insert page break & line 3

Back
Top