Lookup a value across multiple sheets and return all sheet names the value appears on.

[BC-GT]

Hi All,

New member here with my first post (in hopefully the right place!) so please feel free to point out if I am making any newbie mistakes in etiquette, etc!

This thread ( https://chandoo.org/forum/threads/h...ts-and-return-addition-all-sheet-names.29525/ ) has proved (almost) the perfect answer to an issue I was having with a need to take a list of parts and check through a collection of product sheets and report all those that each part appears on.

I created the list sheet with all product sheets names (TabNames) and the links to shortcut to them. The product sheets all have the same layout.

I then created another sheet with all part descriptions in Column A and codes (alphanumeric) in column B.

For Columns C through CX (100 columns) I then put in the following formula, slightly amended to take account of the different columns:

{=IFERROR(INDEX(TabNames,SMALL(IF((1=--(COUNTIF(INDIRECT("'"&TabNames&"'!$B$1:$B$100"),$B2)>0)),ROW(TabNames) - MIN(ROW(TabNames)) + 1),COLUMN(B2))),"")}

At first I thought this worked properly as the first few parts I checked had all product sheets they appeared on, listed. Then it became clear that some parts had none of the sheets listed and some had just some. It's very inconsistent.

I checked already for erroneous leading space characters and even for CHAR(0160), removing them where found, which did improve the accuracy.

I'm stumped but still do not have a correct, working lookup of occurrences.

Can someone offer any advice?

Thanks!


P.S> I did post this to the end of the above thread until I realised that I needed to start a new thread.

 

[bosco_yip]

Perhaps,

Changed "COLUMN(B2)" to "COLUMN(A2)" as below :

{=IFERROR(INDEX(TabNames,SMALL(IF((1=--(COUNTIF(INDIRECT("'"&TabNames&"'!$B$1:$B$100"),$B2)>0)),ROW(TabNames) - MIN(ROW(TabNames)) + 1),COLUMN(A2))),"")}

Regards
Bosco

 

[BC-GT]

Thanks for the reply, Bosco_Yip.

I wasn't sure that this would help (at first look) as Column A is the Description of the part rather than the code (in Column B) and the code is the only one of those that appears on the product sheets that I want to list.

I made the changes anyway and it seems (without checking properly) that it could have fixed it!

I'll report back once I confirm it works.

I think I've earned some 'guru' points from my managers with this as the alternative was weeks of man-hours to manually check.

Cheers!