Merge the searching of sheets together,

[CV12]

Hi,

I hope i can make it clear to you what I want to do by text:

=INDEX(FILTERXML("<F><d>"&SUBSTITUTE(TEXTJOIN(" ";;Sheet2!B1:B5;Sheet3!B1:B5;Sheet4!B1:B5);" ";"</d><d>")&"</d></F>";"//d");MATCH(D8;FILTERXML("<F><d>"&SUBSTITUTE(TEXTJOIN(" ";;Sheet2!F1:F5;Sheet3!F1:F5;Sheet4!F1:F5);" ";"</d><d>")&"</d></F>";"//d");0))

Given this formula, I am looking at Sheet 2, 3 and 4 in the following part:

Sheet2!B1:B5;Sheet3!B1:B5;Sheet4!B1:B5

Because the sheets may vary, I would like to intend them. So it would look something like Sheet 2 up until sheet X (Lets take 6 for example). I just do not want to insert all my seperate sheets in this formula. How to do this?

Thank you in advance.

 

[Jeffrey Mahoney]

In the example below, I referenced the cell E1 on 4 sheets. The sheet tabs must all be contiguous. You can move the sheets in and out of the range of sheets to include or exclude them from the calculation. In the past, I have added a sheet to the left of the series called "BEG" and a sheet at the end called "END" to keep me straight. I included those two sheets in the formula so it would change; in the example at the bottom
=TEXTJOIN(",",TRUE,Sheet1:Sheet4!E1)

=TEXTJOIN(",",TRUE,BEG>:END!E1)