Link to First Row With a Blank Value in a Specific Field

Marc Arnold

I have two sheets, and I wish to link to a value in one sheet from a cell in another. Specifically, I want to include the value in Milestone, 2 in the 1st screenshot, as it is the first value with a blank date in Baseline Completion Date. The value would be linked to 6, 3 (Stage) in the 2nd screenshot. I tried using an, INDEX, COUNT combination, but I cannot determine the proper syntax.

Screenshot_1.png

Screenshot_2.png

Paul Newcome

Try this...

=INDEX(COLLECT({Other Sheet Milestone Column}, {Other Sheet Date Column}, @cell = ""), 1)

Marc Arnold

Thanks, Paul! That was perfect!

Paul Newcome

Happy to help. 👍️

Marc Arnold

I was also able to complete the entire string I was attempting to create, thanks to you!

="Milestone " + COUNT({Milestone} + 1) + " of " + COUNT({Milestone}) + " - " + INDEX(COLLECT({Milestone}, {Completion Date}, @cell = ""), 1)