Locate a cell that is to be determined by my User's entry into another cell

New member
I have loan calc & amortization table(starting balance,interest rate and start date) all set.
For further functions I will need to use some of this info and this is where I am stuck.
Since the User's will have different Loan Start Dates, the cell that reflects their Current Balance changes accordingly.
I need to figure out how to
a) locate the one cell that reflects their Current Balance("B18:B377")
b) have data transfer from "A1" into this cell. This cell is within "B18:B377" and has circular references since it is built in to the loan amortization table.
Thanks

Ken Puls

Staff member
You may want to upload a sample workbook for us to look at here. I'm not sure I follow why you need to use circular references for this, and honestly, if you can avoid them, I would.

New member
Sample attached for you

Thanks for the reply Ken! Much appreciated. Sample workbook is attached.
Page 1 allows the user to input their loan details.
I am looking to do a projected Home Equity Calculation
ie. Current Market Value * percentage(2% over 5 years would be 1.104) - loan balance 5 years from today
Because this will vary, I don't know how to input the loan balance 5 years from now.
The function works on this sample since we know when 5 years is from today. I manually entered C118 into the formula.

Please let me know if this is not clear enough for your assistance.

Thanks again,
Chris

Attachments

• HomeEquityCalc.xlsx
103.2 KB · Views: 10

Ken Puls

Staff member
Cool...

Here's the formula: =(D8*1.104)-VLOOKUP(EDATE(\$F\$16,12*5),LoanCalc!\$B\$18:\$I\$377,2,TRUE)

And he's how it works:

EDATE is a function that returns a the date x months before/after the start date. So by adding 12*5 months to your data in F16, we get the date we need. (Today that's 12/13/2017)
We use that in a VLOOKUP that looks up the date in your table of data. The TRUE at the end signifies to find the closest match without going over, which gives us 12/1/2017 since 1/1/2018 is past that. It then returns the 2 column of the provided range, which is the principal.

I then just nested the VLOOKUP into your formula.

New member
You are the Man!!

This is perfect...thanks Ken!
Cool...

Here's the formula: =(D8*1.104)-VLOOKUP(EDATE(\$F\$16,12*5),LoanCalc!\$B\$18:\$I\$377,2,TRUE)

And he's how it works:

EDATE is a function that returns a the date x months before/after the start date. So by adding 12*5 months to your data in F16, we get the date we need. (Today that's 12/13/2017)
We use that in a VLOOKUP that looks up the date in your table of data. The TRUE at the end signifies to find the closest match without going over, which gives us 12/1/2017 since 1/1/2018 is past that. It then returns the 2 column of the provided range, which is the principal.

I then just nested the VLOOKUP into your formula.