Only show sheet name in list if the name is stored in a named range

[lukestkd]

Hi all,
I have the following formula for showing the sheet names in a vertical list:

Please Login or Register  to view this content.

I need to only show the sheet name if it is within the named range 'classcodes', if it isn't I don't want it to show, BUT, also don't want an empty row.

Any ideas?

Cheers Luke

[Harribone]

Create a sheet with a list of the sheet names in Col B starting in B2 (Lets call it SHEETNAMES for this example).
Then in A2 type =IF(IFERROR(VLOOKUP(B2,classcodes,1,0),0)=B2,MAX($B$1:B1)+1,"") and drag drown
If the sheet name is in the classcodes list it will assing it the next available lowest number. Note I assume the first column of the classcodes range contains the sheet names. this sheet can be hidden once done to keep your work tidy.

On the sheet where you want to sheet the sheetnames in Col A type 1,2,3 etc going down from A2 (assuming you want a header row). Continue untill you have the same number of possible sheet names. This column can be hidden. Then in B2 type =IFERROR(VLOOKUP(A2,SHEETNAMES!A:B,2,0),"") and drag down as far as needed.

This will then show available sheet names without any blank rows inbetween.

[Tony Valko]

Assuming that SheetNames is a named range of cells and that both SheetNames and ClassCodes are 1 dimensional arrays (single rows or columns).

Array entered** in A2:

=IFERROR(INDEX(SheetNames,SMALL(IF(ISNUMBER(MATCH(SheetNames,ClassCodes,0)),ROW(SheetNames)),ROWS(A$2:A2))-MIN(ROW(SheetNames))+1),"")

Copy down until you get blanks.