Results 1 to 5 of 5

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,274
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    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
    Attached Files Attached Files

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,274
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5

    You are the Man!!

    This is perfect...thanks Ken!
    Quote Originally Posted by Ken Puls View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •