Formula shorter

[Chief Ace]

Kindly wanted to know if there's any other way to make this formula shorter below?

=IF(COUNTIF(Sheet1!$A$3:$A$16,Sheet5!A3),"duplicate on first sheet",IF(COUNTIF(Sheet2!$A$3:$A$16,Sheet5!A3),"duplicate on second sheet",IF(COUNTIF(Sheet3!$A$3:$A$16,Sheet5!A3),"duplicate on third sheet",IF(COUNTIF(Sheet4!$A$3:$A$16,Sheet5!A3),"duplicate on fourth sheet",""))))

I have a file that have a multiple sheets (sometimes goes beyond 10 sheets) that we tracked down the invoice number to make sure we do not enter the same invoice number on any other sheets. If we do the formula above tells me that there was a duplicate invoice number on first sheet or second sheet....etc (sample file attach) by using a helper column. The reason we have a multiple sheets is because we send it to another agency almost every other three working days. Like stated above question, is there another way of making the formula shorter that works the same way? Kindly appreciate any guidance or/and help on this matter. Thank you.

 

[bosco_yip]

Try.......

1] In Sheet 5 E3, formula :

=IFERROR("duplicate on "&INDEX({"first";"second";"third";"fourth"},MATCH(1,COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4"}&"'!A3:A20"),A3),0))&" sheet","")

..............

2] See attached file

Regards
Bosco

 

[Chief Ace]

bosco_yip

WOW...talking about me being a newbie....never knew about "INDIRECT"...

Thank you so much greatly appreciate the shorter version....it works like a charm.... :D

Regards and have a great weekend!!!

 

[SirJB7]

Hi, Chief Ace!
Just a warning on using INDIRECT function. It's a volatile function, that's to say, it gets recalculated not only when cells in dependency chain change their values but whenever any cell does. This normally wouldn't be relevant but if you have a lot of cells using INDIRECT -and by a lot I mean a lot!- you may experience slow recalculation. Check if this happen.
Regards!

 

[Chief Ace]

SirJB7

Thank you for the heads up!!!!

Cheers
Chief Ace

 

[Chief Ace]

I have another question concerning about the formula above. I use the formula that bosco_yip had provided and it works wonderful until i find out that the formula only give you the very first sheet that has a duplicate:

example: if i'm typing the invoice number on sheet 5 then it would tell me that there's a duplicate on sheet 1 or sheet 2...etc. But not multiple returns

Kindly wanted to know if it's possible to have multiple returns (ie: duplicate on first, second and fourth sheet) if the invoice number that I type in sheet 5 exist on other sheets. Please see attachment. Thank you.

r/
Chief Ace

 

[bosco_yip]

.......if it's possible to have multiple returns (ie: duplicate on first, second and fourth sheet) if the invoice number that I type in sheet 5 exist on other sheets. Please see attachment.
Chief Ace

The return of multiple sheet names requirement need helper columns, formulas used as following .

1] In Sheet5, "helper" E3 formula copy across and down:

=IFERROR(INDEX({"first";"second";"third";"fourth"},AGGREGATE(15,6,ROW($1:$4)/(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4"}&"'!A3:A20"),$A3)=1),COLUMNS($A$1:A$1)),0),"")

2] In Sheet5, "Name of dulpicate sheet" D3, formula copy down:

=IF(COUNTIF(E3:H3,"?*")>0,"duplicate on "&SUBSTITUTE(TRIM(E3&" "&F3&" "&G3&" "&H3)," ",", ")&" sheet","")

3] Please check other sheets formulas as per attached file.

4] Hide helper columns if you need.

Regards
Bosco

 

[NARAYANK991]

Hi ,

Another option.

Narayan

 

[Chief Ace]

wow...just wow...you guys are amazing....

bosco_yip: your recommendation works perfectly. I greatly appreciate the help that you provided.

NARAYANK991: Thank you for the suggestion and using wingdings. This will help a lot to the spreadsheets that i'm trying to accomplish.

Again...thank you always for the superb help from the member and Excel Ninja's.

Regards
Chief Ace