DGET fxn with nested TODAY fxn error

[NewGuy44]

Hello everyone.

I have a specific goal I am trying to achieve and I'm hoping you can help me.

My Goal: I am using a basic loan amortization schedule spreadsheet to tract a loan. I want to add a dynamic cell that displays the current balance of the loan based on the current month.

My incorrect solution: I have recently discovered the DGET function and am able to achieve my goal if I simply type "=A3" into cell E2 . However, once I add the TODAY fxn to make it dynamic, it gives an #VALUE error.

If there is a simpler way to achieve the correct result, please let me know.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Amort Schedule[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[TD]Balance[/TD]
[TD][/TD]
[TD][/TD]
[TD]TODAY Fxn[/TD]
[TD]DGET Fxn[/TD]
[/TR]
[TR]
[TD]Jul-18[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aug-18[/TD]
[TD]4900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sep-18[/TD]
[TD]4800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct-18[/TD]
[TD]4700[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov-18[/TD]
[TD]4600[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help would be appreciated.

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E	F
1	Amort Schedule				Date	Balance
2	Date	Balance			7/14/2018	5000
3	Jul-18	5000
4	Aug-18	4900
5	Sep-18	4800
6	Oct-18	4700
7	Nov-18	4600
Sheet4

Cell Formulas
Range	Formula
E2	=TODAY()
F2	=VLOOKUP(E2,$A$3:$B$7,2,1)

(dates are 1st of month)

 

[NewGuy44]

This?

Excel 2010

	A	B	C	D	E	F
Amort Schedule	Date	Balance
Date	Balance

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/14/2018[/TD]
[TD="align: right"]5000[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]4900[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]4800[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]4700[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]4600[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=TODAY()[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=VLOOKUP(E2,$A$3:$B$7,2,1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



(dates are 1st of month)

Unfortunately, this gives me a #REF . Also, I just realized that the dates in A3:A7 are nested IF functions that give the date as the output using "DAY(fpdate)". Not sure if that changes things.

Thank you for the help so far!

 

[sheetspread]

How do the dates appear in the formula window when you paste as a value?

 

[Aladin Akyurek]

[TABLE="class: grid, width: 395"]
<tbody>[TR]
[TD]Amort Schedule[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Balance[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7/1/2018[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]7/1/2018[/TD]
[TD="align: right"]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/1/2018[/TD]
[TD="align: right"]4900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2018[/TD]
[TD="align: right"]4800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/1/2018[/TD]
[TD="align: right"]4700[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11/1/2018[/TD]
[TD="align: right"]4600[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If you insist on using DGET, in E2 enter:

=EOMONTH(TODAY(),-1)+1

not just:

=TODAY()

In F2 enter:

=DGET($A$2:$B$7,"balance",$E$1:$E$2)

You can also invoke a fast VLOOKUP instead of DGET. This allows using just

=TODAY()

in E2.

F2 becomes:

=VLOOKUP(E2,$A$3:$B$7,2,1)