Finding unique values from Column, then listing them in another sheet

Frank S.

@Leibel S

Formula Update: =IFERROR(INDEX(DISTINCT(COLLECT({ONE Program Approved Change Requests Range 2}, {ONE Program Approved Change Requests Range 2}, AND(@cell <> "", ISNUMBER(IFERROR(VALUE(SUBSTITUTE(@cell, ".", "")), "A")))), [Row #]@row), ""))

Frank S.

@Leibel S

Here is my new formula, I copied the wrong one, in my first message, my apologies.

=IFERROR(INDEX(DISTINCT(COLLECT({ONE Program Approved Change Requests Range 2}, {ONE Program Approved Change Requests Range 2}, AND(@cell <> "", ISNUMBER(IFERROR(VALUE(SUBSTITUTE(@cell, ".", "")), "A")))), [Row #]@row), ""))

Still get the same error.

Frank S.

@Leibel S

I copied over the wrong formula, my apologies.

Here is the correct formula:

=IFERROR(INDEX(DISTINCT(COLLECT({ONE Program Approved Change Requests Range 2}, {ONE Program Approved Change Requests Range 2}, AND(@cell <> "", ISNUMBER(IFERROR(VALUE(SUBSTITUTE(@cell, ".", "")), "A")))), [Row #]@row), ""))

Still get the same error.

Frank S.

@Leibel S

Sorry for the multiple posts, was having some connectivity issues.

Leibel S

@Frank.Smith

Can you send screenshots of the 2 sheets?

As well as the cross sheet references you created.

Frank S.

Screen Shot 2021-05-10 at 7.44.00 AM.png

Screen Shot 2021-05-10 at 7.45.09 AM.png

Here are the screenshots, thanks for the help

Leibel S

@Frank.Smith

Looks like I was missing a parentheses in my formula.

The below should be correct:

=IFERROR(INDEX(DISTINCT(COLLECT({ONE Program Approved Change Requests Range 2}, {ONE Program Approved Change Requests Range 2}, AND(@cell <> "", ISNUMBER(IFERROR(VALUE(SUBSTITUTE(@cell, ".", "")), "A"))))), [Row #]@row), "")

Frank S.

@Leibel S

Yeah, that worked. I was pulling my hair our trying to see where I was missing something.

You are the best!

I have another question about the earlier date formula is there a way I can only display dates that are today or earlier?

here is the current formula: =IFERROR(INDEX(DISTINCT(COLLECT({ONE Program Change Request Backlog Range 1}, {ONE Program Change Request Backlog Range 1}, ISDATE(@cell))), [Row #]@row), "")

Leibel S

see below

 =IFERROR(INDEX(DISTINCT(COLLECT({ONE Program Change Request Backlog Range 1}, {ONE Program Change Request Backlog Range 1}, AND(ISDATE(@cell), @cell <= TODAY()))), [Row #]@row), "")

Frank S.

@Leibel S

I jumped the gun, when I put in the formula I get all blank cells.

 =IFERROR(INDEX(DISTINCT(COLLECT({ONE Program Change Request Backlog Range 1}, {ONE Program Change Request Backlog Range 1}, AND(ISDATE(@cell), @cell <= TODAY()))), [Row #]@row), "")

Leibel S

You sure you have dates today and earlier?

Works great by me.

Frank S.

@Leibel S

Screen Shot 2021-05-10 at 9.42.43 AM.png

Screen Shot 2021-05-10 at 9.43.08 AM.png

Screen Shot 2021-05-10 at 9.43.20 AM.png

Here are some screen grabs, sorry for being such as pain.

Leibel S

none of those dates are today or earlier

Frank S.

@Leibel S

You are correct, my bad. Now I feel real dumb.

Frank S.

@Leibel S

So if I want dates today or in the future, just change <= to >= correct?