#VALUE! Error with INDIRECT Formula

Hi Everyone!

Good day!

I would like to seek help on this forum regarding the problem I'm encountering with INDIRECT function (see attached file). I'm not new on using INDIRECT, however, the situation like that on the sample file puzzles me. Is there any rule on INDIRECT function that I'm not aware of?

Goal

1.PNG

I have a list of names on range A3:A9. I would like to make a list composed of unique names (no name is repeated) at range D3:D9. The expected output is shown at range C3:C9.


Problem:
I'm trying to refer a range that is constructed with INDIRECT, however, the formula returns an error when generating the range (see the following figures).

Formula showing the constructed range
2.PNG

Formula showing the error
3.PNG


Code:

{=IF(ROW()=ROW($B$3),$B4,IF(SUM((--(INDIRECT("D3:D"&(ROW()-1))=$B4)>0)),"",$B4))}

I tried skipping the INDIRECT function and the code works (range E3:E9) proving that there's no problem with the programming (see figure below).
INDIRECT Function Skipped
4.PNG



I know I could use the COUNTIF as replacement for the SUM-INDIRECT for my goal, still, I want to know why the formula doesn't work.

Thanks in advance!


Warmest Regards,
Emerson

Hello Pepe Le Mokko!

Thanks for the reply.

Originally Posted by Pepe Le Mokko

Try =IF(COUNTIF($B$3:B4,B4)=1,B4,"") in C3 and pull down

Yes, I've also mentioned it at the bottom part of my post. The purpose of my post is to know the explanation why the INDIRECT is returning an error.

Originally Posted by Pepe Le Mokko

As for your formula INDIRECT returns a range of text that you are trying to sum, returning the VALUE error

I used double minus ('--') in the formula to convert the TRUE/FALSE to number 1/0 so I think it is valid. I've also shown on the last figure a table with a column that the INDIRECT function part is skipped and the result returned is as intended (see column E of the last figure) proving that there's nothing wrong in the code.


So any more ideas why the code returns an error?


Thanks a lot!

Emerson

Up

I'm really curious what's happening in the code. Thanks!