Creating a dynamic worksheet

[jd92]

I'm working through chandoo currently trying to expand my excel knowledge unfortunately it's not happening as fast as I would like.

The worksheet I'm currently working in depicts real estate information regarding sales prices, purchase prices, returns, npv's and irr's, as well as some other pertinent info. I need some help making this worksheet flow more dynamically when certain variables are changed. I'm trying to make all the data based off of what number is selected from the drop-down menu in cell "F1" which is the Sale Year.

The main issue I'm working on right now is making cells "C7 -P7" dependent on what sale year is selected in "F1". I'll try to put this as plainly as I can, I would only like for the expenditures to be visible in cells "C7-P7" prior to the sale year I've selected.
EX.. If my chosen sale year is year 6, I only want expenditures to show from years 1-5 in the C cells.

Realistically I would like the other variables in the worksheet to flow with this "sale year" as well, such as NPV, IRR, outflows in cells "B19-B20" etc.

Any help is greatly appreciated! Also if you have any suggestions or personal touches that you feel may make this flow better I'm absolutely open to your edits! Thanks

 

[Nebu]

Hi:

The formulas on your return tab is not making sense.

• How am I going to make the formula dynamic if your variables '1Renting Tenant'!$B$60 & '1Renting Tenant'!$B$61 is fixed?
• How is your Mortg. Balance $125628, what is the logic behind this?
• In your formula you are using E19 , but cell E19 is blank in your tab, explain why?

Thanks

 

[jd92]

Hi:

The formulas on your return tab is not making sense.

• How am I going to make the formula dynamic if your variables '1Renting Tenant'!$B$60 & '1Renting Tenant'!$B$61 is fixed?
• How is your Mortg. Balance $125628, what is the logic behind this?
• In your formula you are using E19 , but cell E19 is blank in your tab, explain why?

Thanks

Sorry for the confusion, I'll try to clarify.

The variables in B60 and B61 are fixed costs that I'm assuming for the formulas in the return tab. Maybe dynamic wasn't the proper word. My goal is to have the cells B7-Q7 populate based on what year I'm using as the sale year in F1. Meaning that If I select year 8 as the sale year, I would like to see expenditures in year 1-7 with year 8 populating the Sale price( (B1)*(D19) ).

The cell B2 containing the mortgage balance refers to the remaining balance in a given year. So if I choose year (X) as the sale year, I would like to see (XXX) remaining dollars for the mortgage balance based on the chosen sale year. This information is taken from the (% ownership) tab; X years = XX months.

Cell E19 is a mistake, I had a problem with circular references but it is corrected in the uploaded spreadsheet.

Thanks for taking a swing at this thing, Sorry if I'm not being too clear, I appreciate the effort though!

 

[Nebu]

Hi:

I have done it for first set of cash flows, replicate it for the second set.

Thanks

 

[jd92]

Seriously impressed. Thanks again!!!