Update Range based on Lookup Value

Hello:

I need some assistance with populating a range (i.e., value lookup and calculation updates) based on a cell value.

Please see attached XLSX that illustrates an example. I will summarize the requirements in bullet format.

Example #1:
- Cell B1 contains a drop-down menu with values between 1 and 30. In this case, value = 7 has been selected.
- Now, starting in cell A4, I want to display the range of "Years" based on the value in B1.
- Thus, given that B1 = 7, range A4:A10 lists values from 1 through 7.
- Also, column B includes a (notional) calculation. Therefore, the formulas need to be added to B4:B10.
- Lastly, I have "borders" (gray) that are currently applied manually to the cell range A4:B10. I'd like the application of borders to be automated, too.

Example #2:
- Now, let's say I change the value from "7" to, e.g., "15" in cell B1. The following should happen:
1. In column A, I will show values from 1:15 in cell range A4:A18.
2. In column B, the formulas will be applied showing a notional value = 22.5 in B18.
3. Borders are now applied to the range A4:B18.

Naturally, the same 3 steps (listed in example #2) will need to be applied for any value in the drop-down menu in B1.

How can this be accomplished?

Thank you,
EEH

Hi AliGW:

Sorry for the delayed response... for some reason, I didn't get an email that a contributor (you) responded to the post. Again, sorry for the delay.

I downloaded your Excel file... your solution is PERFECT!!!! Thousand thanks for helping me out on this one.

Since today is New Year's Eve, I wish you all the best for 2022. Many thanks for the help you offered me in the "old" year.

Happy New Year,
Tom