Copy the value in a range of cells to another cell if only one cell in the range has a value

0
votes

I have a range of cells M4:M18. In that range of cells is a formula referencing another table, there will only be one date in this range of cells. I'm trying to get that one date to populate into cell H12. This is the formula that I have in H12:

=IF(COUNTA(M4:M18)=1,INDEX(M4:M18,MATCH("*",M4:M18,0)),"")

I've also tried:

=IF(COUNTIF(M4:M18,"?")=1,INDEX(M4:M18,MATCH("?",M4:M18,0)),"")

This formula returns a black cell in H12. I read a previous post regarding this same issue, but I can't seem to get it to work. The first cell in this range could be empty.

Any help would be greatly appreciated.

Thanks

M4:M18 ='Step 2 Triage_Tbl'!$Q3 and are formatted as m/d/yy;;""

and

3
excelexcel-formula
It depends.......are M4:M18 filled with formulas and only one displays a non-null or does only one of the cells contain a formula?? – Gary's Student
Thanks for your response. All cells have a formula that is wrapped w/ IFERROR to show a blank cell if there is no calculation. – TMusky
See below. It can locate data that is not "blank" – Gary's Student

3 Answers

0
votes

OneDate UDF

In VBE (ALT+F11) insert a module into your worksheet and copy the code into it.

In Excel in cell H12 type =OneDate(M4:M18). You might need to format H12 as Date if not already formatted. 

Option Explicit

Function OneDate(CheckRange As Range) As Date
    Dim rng As Range
    For Each rng In CheckRange
        If IsDate(rng.Value) Then OneDate = rng.Value: Exit For
    Next
End Function

You might need to adjust the Macro settings for this to work:

File > Options > Trust Center > Trust Center Settings > Macro Settings

enter image description here

0
votes

To return only one (1) date in a range M4:M18, try..........

In N2, enter formula :

=IF(COUNT(M4:M18)=1,INDEX(M4:M18,MATCH(1,INDEX(0+ISNUMBER(M4:M18),0),0)),"")

enter image description here

0
votes

Say M4:M18 is completely filled with formulas, but only one of the formulas gives data, the others, null. This will locate the data:

=MATCH(TRUE,M4:M18<>"",0)

and this will retrieve the data:

=INDEX(M4:M18,MATCH(TRUE,M4:M18<>"",0))

enter image description here

This works for text or numbers or dates.