How to skip one row?

[BaGRoS]

I have more than 100 sheets. I extract their name by Name Manager where SheetNames=

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

and put them in a table:

=INDEX(SheetNames,A75)

Now I would like to be able to decide when to add a sheet with the name Conti in the name and when not. I need to skip a row when I have Conti in the name.
I have started something like this:

=IF($N$82=1,IF(ISNUMBER(SEARCH("Conti",INDEX(SheetNames,A75))),1,INDEX(SheetNames,A75)),INDEX(SheetNames,A75))

however, I don't know how to skip a row when there is 1

Some ideas?

 

[Fluff]

How about

=TOCOL(IF(N82=1,FILTER(SheetNames,ISERROR(SEARCH("Conti",SheetNames))),SheetNames))

Put this in one cell & clear all the cells below & it will spill down

 

[BaGRoS]

How about

=TOCOL(IF(N82=1,FILTER(SheetNames,ISERROR(SEARCH("Conti",SheetNames))),SheetNames))

Put this in one cell & clear all the cells below & it will spill down

I don't have =TOCOL in my Excel.

 

[Fluff]

Are you putting the formula into a Table?

 

[BaGRoS]

Are you putting the formula into a Table?

Yes,

 

[Fluff]

Are you happy to just use it in a normal range & not a table, it will be a lot simpler & easier?

 

[BaGRoS]

Yes, no problem

 

[Fluff]

In that case you can use

=TRANSPOSE(IF(N82=1,FILTER(SheetNames,ISERROR(SEARCH("conti",SheetNames))),SheetNames))

as you obviouls don't have the Tocol function yet.

 

[BaGRoS]

In that case you can use

=TRANSPOSE(IF(N82=1,FILTER(SheetNames,ISERROR(SEARCH("conti",SheetNames))),SheetNames))

as you obviouls don't have the Tocol function yet.

Yes, fantastic.

Now I have to find how set Data Validation only for filtered values :P

 

[Fluff]

What cell is the formula in?