Forum Discussion
Named table referencing visible values
I have an amortization table with a named range 'Payment Schedule' with named columns.
The formula in Payment Number displays an incremental payment number when the End Balance is > $0, otherwise the Payment Number is "" (blank).
I'm trying to identify when the Payment Number is first blank / empty - in this example, Row 16.
However, whenever I reference [@[Payment Number]] in a formula in Row 16 returns a value of 3, not the visible/displayed value of "" , resulting in the 'Closing Balance' milestone comment to be incorrectly repeated.
How can I best reference this without using VBA?
The formula I'm basically writing is if [@Payment Number]] = "", then [@[Milestone]] = "".
Thank you
Going by all the named ranges and sophisticated formulas, you clearly know your way around Excel. But perhaps you've not used the IFS function before. In general, it makes for slightly clearer logic when multiple conditions need to be tested.
Here's what I came up with in your column M. (I've attached a revised copy, as I downloaded your file to my computer to work directly on my own computer rather than in the cloud.)
=IFS([@[Payment Number]]=1,"Opening Balance",[@[End Balance]]>0,"",SUM(K18:K19)=0,"",[@[End Balance]]=0,"Closing Balance")Essentially, I added a different condition, that being two rows sequentially (the current and the prior) totaling $0.00, to leave the row blank. That way, only the first instance of "End Balance" being zero gets the result "Closing Balance"
You could combine the second and third conditions with an OR, if desired, since they both lead to the "" result, but that might make it harder to debug in the future.
FYI, in case you're not familiar with IFS, the formula "stops" once it reaches a condition that is met, so the sequence in which you list them is critical. That's why the sum of those two rows being zero precedes the one that elicits "Closing Balance" -- otherwise, every row with zero gets "Closing Balance" (as you discovered).
Unless this contains confidential info--in which case just create one with dummy data--please post a copy of the actual spreadsheet on OneDrive or GoogleDrive, with a link here granting access to it. (Images have limited utility.)
You started to write your formula, but somehow or other it ended up being incomplete in your post, leaving us nothing to work with.
- Thank you mathetes, apologies I'd not completed the formula, updated now.
The file can be found here, it's not particularly elegant
https://1drv.ms/x/s!AlAwFcyIJ3fVgedWduabFH6pOOkJ9g?e=yLm0xE
Appreciate your assistanceGoing by all the named ranges and sophisticated formulas, you clearly know your way around Excel. But perhaps you've not used the IFS function before. In general, it makes for slightly clearer logic when multiple conditions need to be tested.
Here's what I came up with in your column M. (I've attached a revised copy, as I downloaded your file to my computer to work directly on my own computer rather than in the cloud.)
=IFS([@[Payment Number]]=1,"Opening Balance",[@[End Balance]]>0,"",SUM(K18:K19)=0,"",[@[End Balance]]=0,"Closing Balance")Essentially, I added a different condition, that being two rows sequentially (the current and the prior) totaling $0.00, to leave the row blank. That way, only the first instance of "End Balance" being zero gets the result "Closing Balance"
You could combine the second and third conditions with an OR, if desired, since they both lead to the "" result, but that might make it harder to debug in the future.
FYI, in case you're not familiar with IFS, the formula "stops" once it reaches a condition that is met, so the sequence in which you list them is critical. That's why the sum of those two rows being zero precedes the one that elicits "Closing Balance" -- otherwise, every row with zero gets "Closing Balance" (as you discovered).
