Named table referencing visible values

[ExcellerateSolutions]

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

[#2]

@ExcellerateSolutions 

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.

[#3]

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 assistance

[#4 · Accepted solution]

@ExcellerateSolutions 

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).

[#5]

@mathetes - perfect thank you so much! I had dabbled with IFS last night in a separate file with no luck. Your approach using SUM is much cleaner than my thinking also.
Thanks again, I hope I can contribute back to this community as you have

[#6]

@ExcellerateSolutions 

Your approach using SUM is much cleaner than my thinking

Truth be told, that was about my fifth attempt at writing a condition that worked cleanly in your circumstance. I often find, especially with multiple conditions, that it takes some "playing" with alternative methods.

Thanks again, I hope I can contribute back to this community as you have

You're very welcome. I realized earlier today that it's been nearly three years since I stumbled on this place, posted a question, then hung around to answer some. With other regular folks who answer, I've discovered this can be habit forming. And a great learning experience too, working to solve problems such as the one you presented today.