Mystified by name range adjustments

[AB33]

I have got the attached spread sheet from John Walkenbach’s book. I could not figure it out how he got "Number of Periods:" 240.00 automatically change.
I have looked at the range names. There is no dynamic range. All it shows on the range name is the “payment period” is in the range A9-A45. If I change the values in B4, all rows on the spread sheet are adjusted automatically. For e.g. at the moment the period number is 240. The rows go down from rows 10 to row 249, but If I change B4 to 24 periods, not the only the rows are adjusted on Payment period Column to rows 10 to row 33, but all other columns as well.
How is this possible, and where is I am missing? Please help

[JosephP]

there is conditional formatting hiding the data in the rows after the specified number of periods

[Spencer101]

The comment attached to cell A8 gives the answer.

[AB33]

Sorry chaps! I still do not get it. The payment period as per named range is rows 9-45, so where does row 486 comes from? How do CF affects the formula?

=IPMT(I.R*($B$3/12),Payment_Period,Term,-Loan)

If I were to create a new sheet, how do I incorporate the formula in to mine? I have created similar spreadsheet and use the same CF as the sample one, but does not do anything. There is one CF, ie =$A9>$B$4 and applies to =$A$9:$H$489

Please help

[JosephP]

the CF does not alter the formula, it just hides the results. select any of the blank looking cells and you will see that the formulas are still present

[Spencer101]

Press Ctrl+A to select all and then look at row 250 downward (whilst everything is selected).

You'll see that the conditional formatting just turns the text white and the cells LOOK empty when not highlighted like this.

The formulas are referring to the value in cell B4 and therefore if the value in column A is larger than the value in cell B4 the formulas result in errors. These errors are also hidden by the conditional formatting turning the cell text white.

[AB33]

Spencer! Thanks for your help!
I have added a new sheet to the above attached sheet. How does the CF affect the formula? If I change "Mine" sheet in B4 to let say 24, the monthly values in columns are wrong. Could you please show me the adjustment on mine?

[ChemistB]

Okay, let me try.
There are two places where your Number of periods are used (defined name Term), in Col D and in the conditional formatting.
The conditional formatting is =$A9>$B$4 which changes the text to white for all rows greater than the term period.
I removed the conditional formatting to show what it really looks like. Nowhere are the formulas changing.
I hope this helps.

[Spencer101]

First off, your conditional formatting has not been set fully. You have the formula side of it in place, but no actual condition. You need to change it so the text turns white if the condition is met.

Secondly, You know this spreadsheet better than I do, so you'll have to point out exactly where is looks like it's going wrong and I can have a look at it from there.

I wont be able to look at this right away though as need to get to the shops to buy my dinner before they shut

[AB33]

Spencer and Chemist!

Thanks all for trying to help!

You are right! There is nothing wrong with my formulas. My CF is wrong.
Now, I do not know where I am getting wrong. Actually followed the steps on CF on Sample sheet, but do not know what is wrong.
In the format, there is nothing is selected. If I go to edit format, text is not selcted.

Chemist! After you removed CF, I know now, the problem is with CF, not formula. As I am struggling getting CF right, Could you please kindly put the CF back on the second sheet-108KB. The first attachment is wrong.
Thanks again for your patiency

[ChemistB]

Okay, I dragged on your formulas down to row 487 and then corrected your conditional formatting. You had the right range and the right formula but didn't format it as white font. Is this what you are looking for?

[Spencer101]

I pointed that out in post #9, although perhaps not as clearly as ChemistB did

[AB33]

Spencer and Chemist! Thanks all for your time!

Chemist. It did the trick, although, there was a problem with part of the formula as the name range was stetched up the last row. I have now corrected it and all perfect.

Spencer. Yes,You did point out. It was my stupidity, not you solution, was the problem.
Turning the white font was on the CF was doing my heading. I have to admit that I am a big fun of CF
Thanks again! I will now close this thread as solved.